SQL SERVER Data Dictionary on a Shoestring budget

As a developer you begin a new project and one of the most important things to come up to speed on is the data model for the project. You ask around about where you might find data dictionary documentation and you get several answers including:

· Ask the DBAs, they might know where it is kept.

· If there is any documentation it should be out on the SharePoint site.

· There wasn’t enough time allocated in the project to let us build this out.

· The project didn’t have any budget to purchase any tools to produce a data dictionary.

This can be rather frustrating especially when you need to understand the data in the database in order to be productive at the tasks you have been given for the project. If you could just have some current meaningful table and column descriptions that would go a long way to help you come up to speed.

The following simple approach can be incorporated into any project that uses SQL Server as its database.

Step 1 – Make the data dictionary be part of your database

SQL Server allows for the use of extended properties on many objects in the database including tables and columns. In SQL Server Management Studio (SSMS), simply right click on any table to see its properties. Extended Properties is the section to select in order to see any current defined properties or to add any new properties. The main thing is to standardize your property names like “Description” for any comments. For data warehouse projects another nice property to add would be “Source” to track the source mapping of where the data came from to populate the table. Both of these properties work well for columns also.

SSMS is probably not the most convenient place for entering the descriptions. It would be better if the descriptions were in the DDL. Luckily we have T-SQL functions to add and update extended properties.

Step 2 – Add the Extended Properties to the DDL Scripts

The T-SQL function for adding an extended property is sys.sp_addextendedproperty.

‍Table Example

‍EXEC sp_addextendedproperty @name=N'Description', @value=N'Job Queue provides a queuing mechanism for executing jobs in the order of priority for a given group of Jobs.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'JobQueue'

GO

‍Column ExampleEXEC sp_addextendedproperty @name=N'Description', @value=N'Indicates which Jobs are queued to executed (Q), executing (E), completed (C) execution or failed (F) execution.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'JobQueue', @level2type=N'COLUMN',@level2name=N'ProcessingInd'

GO

The key parameters have been highlighted. For a further explanation and more examples see: http://msdn.microsoft.com/en-us/library/ms180047.aspx

‍It is worth noting that if you manage your database DDL as Visual Studio database project that the database migration scripts to an excellent job of handling updates to extended properties by producing scripts that use sys.sp_updateextendedproperty.

Step 3 – Extract the Extended Properties to Create the Data Dictionary Documentation

Now that you have the properties stored in the database, you can easily retrieve them out using the fn_listextendedproperty function. The following example will select all table extended properties in the dbo schema. To limit this just to the extended property of “Description”, a filter can be added.

SELECT objname, name, value

FROM fn_listextendedproperty(NULL, 'schema', 'dbo', 'table', default, NULL, NULL)

To extract specific extended properties, just add the filter to the property you need such as WHERE name = 'Description'.

Column descriptions can be obtained utilizing fn_listextendedproperty with a slightly different set of parameters. In the example below, the table name is added along with the parameter of “column” to get the list of column extended properties.

SELECT objtype, objname, name, value

FROM fn_listextendedproperty (NULL, 'schema', 'dbo', 'table', 'JobQueue', 'column', default);

To extract specific extended properties, just add the filter to the property you need such as WHERE name = 'Description'.

For a further explanation on fn_listextendedproperty and more examples see: http://technet.microsoft.com/en-us/library/ms179853.aspx

‍Now all it takes is to take the output from the above commands and format it into CSV for Excel or as HTML. Here is a small sample of HTML output.

As you can see with a little work, you can keep an up-to-date data dictionary available in SSMS and in a document form that doesn’t cost a huge amount of money. In future posts, I can provide more details on the SQL script that was used to generate the HTML document.