Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
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: 2050 | Views in the last 30 days: 3
 
Related Articles
FORUM

Dictionary Table

Dictionary Table

BLOG

SQLDIY: Gathering A Data Dictionary

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

FORUM

Database Dictionary

Database Dictionary

ARTICLE

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...

FORUM

Stored Procedure Metadata

Query which columns returned by a stored procedure

 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones