SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Get your favorite SSC scripts directly in SSMS with the free SQL Scripts addin. Search for scripts directly from SSMS, and instantly access any saved scripts in your SSC briefcase from the favorites tab.
Download now (direct download link)

Data Dictionary using sys.objects - SQL 2008

By sqldummy79,

I created a script to create a data dictionary in July of 2006 mainly for SQL 2005 and it did work with 2008. However, I realized that it wasn't dynamic enough for a real time data dictionary and had other developers complaining that the dictionary wasn't up to date.

So I created the two stored procedures, code attached and a SQL Server Reporting Service (SSRS) report and when the report is rendered the informationis up to date as of the date the report ran. I'm running the SSRS report using the two stored procedures against a database that has 50 tables and the report rendered within 10 seconds.

Of course the more tables the more time before the report is rendered but at least I won't here that the dictionary is out dated.


So here is what the stored procedures do:

The first stored procedure gets the table name and description, if the description has been added either via the GUI of table design or through the sp_addextendedproperty command.

The second stored procedure provides information on the columns within the table, the information that is returned is the column name, datatype with the length defined, if the column is nullable, default value and the description if added via GUI or sp_addextendedpropery command.


Hope this is helpful to someone.




Total article views: 2142 | Views in the last 30 days: 2
Related Articles

SQLDIY: Gathering A Data Dictionary

In this installment we are looking at assembling a basic data dictionary from the column level meta ...


Dictionary Table

Dictionary Table


Database Dictionary

Database Dictionary


Data Dictionary from within SQL Server 2000

Mindy explores the metadata stored in SQL 2000 to show you how to produce a simple and useful data d...


Stored Procedure Metadata

Query which columns returned by a stored procedure