Monthly Archives: June 2017

Making a code book in sql server - Part 2

Reading Time: 2 minutes

We have already covered how to add a data dictionary to a sql server table by using extended properties (see here). We can go one step further and making a simple stored procedure that will give us quick access to the code book. After building the stored procedure below we can now double click a table and use a keyboard shortcut. I have mine shortcut as Ctrl+5.

Create Procedure [dbo].[GetDataDictionary]

@TableName varchar(200)

	[minor_id], AS [Table Name], AS [Column Name], 
	[value] AS [Extended Property],
	sys.extended_properties AS ep
	inner join
	sys.tables AS t 
	ON ep.major_id = t.object_id 
	inner join
	sys.columns AS c 
	ON ep.major_id = c.object_id 
	  and ep.minor_id = c.column_id
	inner join 
	on infos.table_name = 
	  and infos.column_name =
	class = 1
	and =@TableName /* This is our Table name */


You can see what my shortcuts look like in the picture below.

If we follow the example in the article for building a code book, we can create a table with the iris data set and add a quick code book. Then when we highlight the table [iris] and use our keyboard shortcut of Ctrl+5, you should see this:

Now we can quickly check the definition of a column without having to leave our SSMS window. If you are interested in the other helper stored procedures besides GetDataDictionary, then take a look at this post.

Leave a Comment

Filed under Productivity, Sql Server