Recently I was working on a new database design for a client, and I was asked to prepare a data dictionary of the database. Instead of creating a dedicated single-use document containing the data dictionary, I thought it might be more convenient to design a general mechanism for generating data dictionaries in a database. Such logic could be encapsulated within stored procedures, and the logic would be reusable.
In terms of output, I thought it would be helpful to see the dictionary information in the form of an online SQL Server Reporting Services (SSRS) report. In this way, a data dictionary could be hosted on a company’s departmental reports site, and different team members could refer to the data dictionary report as needed. In addition, because SSRS allows reports to be saved in Microsoft Excel, Microsoft Word, XML, and other formats, the data dictionary information could easily be exported into another report or document.
In this article I present a set of stored procedures that you can use to generate a data dictionary of schemas and tables within a database. I also introduce a simple SSRS report that you can use to publish the data dictionary online. I tested the accompanying code in SQL Server 2008; it also works in SQL Server 2005.
Background
All data dictionary information within SQL Server is set and accessed through extended properties. According to SQL Server Books Online (BOL), extended properties let you “add text, such as descriptive or instructional content, add input masks, and add formatting rules as properties of objects in a database or of the database itself. For example, you can add an extended property to a schema, a schema's view, or to a column in the view. Because extended properties are stored in the database, all applications reading the properties can evaluate the object in the same way. This helps enforce consistency in the way data is treated by all the programs in the system.”
Extended properties have several different levels. SQL Server BOL explains these levels as follows: “For specifying extended properties, the objects in a SQL Server database are classified into three levels: 0, 1, and 2. Level 0 is the highest level and is defined as objects that are contained at the database scope. Level 1 objects are contained in a schema or user scope, and level 2 objects are contained by level 1 objects. Extended properties can be defined for objects at any of these levels.”
To set an extended property, you use a T-SQL command such as sp_addextendedproperty (to add a new property) or sp_updateextendedproperty (to update an existing property). Similarly, you use the sp_dropextendedproperty command to drop an existing extended property in the database.