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.