Monthly Archives: June 2017

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)

as
Begin
SELECT 
	[major_id], 
	[minor_id], 
	t.name AS [Table Name], 
	c.name AS [Column Name], 
	[value] AS [Extended Property],
	infos.[Data_Type],
	infos.[is_nullable],
	infos.[Numeric_Precision],
	infos.[Numeric_Scale]
FROM 
	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 
	INFORMATION_SCHEMA.COLUMNS infos
	on infos.table_name = t.name 
	  and infos.column_name = c.name
Where 
	class = 1
	and t.name =@TableName /* This is our Table name */

End

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.