Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

Special Announcement: SQL Server Performance Tuning Tips and Tricks Webinar
 

Create a SQL Server Data Dictionary in Seconds using Extended Properties


By:   |   Read Comments (17)   |   Related Tips: More > Database Design

Problem

I need a way to create a data dictionary that will stay up to date. I make many changes to the database and I spend more time trying to keep the documentation updated than doing database administration.

Solution

You can create a data dictionary natively with SQL Server for a database in a few seconds if you store the metadata as extended properties. The SQL Server AdventureWorks sample database has numerous extended properties included, so this database serves as a great example.  In this tip we will cover two core topics.  First is sample set of scripts to add extended properties for a table as well as a column.  The second portion of the tip is T-SQL code to generate the data dictionary in HTML format.

The following is a sample script to add extended properties to the database.

Add Extended Properties to a Table and Column

We will add a few extended properties as follows.

/*
The following extended properties already exist in the AdventureWorks database. There is no need to run the 
script against the database in order for the remaining samples to work.
*/ 
USE [AdventureWorks]
GO

--Script to add an Extended Property to the Table
EXEC sys.sp_addextendedproperty 
@name=N'MS_Description', 
@value=N'Street address information for customers, employees, and vendors.' ,
@level0type=N'SCHEMA', 
@level0name=N'Person', --Schema Name
@level1type=N'TABLE', 
@level1name=N'Address' --Table Name
GO

--Script to add an Extended Property to a column
EXEC sys.sp_addextendedproperty 
@name=N'MS_Description', 
@value=N'First street address line.' ,
@level0type=N'SCHEMA', 
@level0name=N'Person', --Schema Name
@level1type=N'TABLE', 
@level1name=N'Address',--Table Name 
@level2type=N'COLUMN', 
@level2name=N'AddressLine1'--Column Name
GO

The extended properties can also be viewed by right clicking on an object in SSMS and selecting properties as shown below:

table properties

Once your database has data in the extended properties, you are ready to run the query to extract the data.  In SQL Server Management Studio select Tools | Options and in the Results to Text section uncheck the "Include column headers in the result set" option. This will keep all of the dashes out of the result set that show up under each column name.

column aligned

Data Dictionary in HTML Format

Run the following script to generate the data dictionary and save the results to a file with an '.htm' extension.

Sample T-SQL Script to Generate a Data Dictionary

You can add additional filters in the WHERE clause if you want to focus on specific items in the dictionary.

Set nocount on

DECLARE @TableName nvarchar(35)

DECLARE Tbls CURSOR FOR
Select distinct Table_name
FROM INFORMATION_SCHEMA.COLUMNS
--put any exclusions here
--where table_name not like '%old' 
order by Table_name

OPEN Tbls

PRINT '<HTML><body>'

FETCH NEXT FROM Tbls
INTO @TableName

WHILE @@FETCH_STATUS = 0
BEGIN

   PRINT '<br>'
   PRINT '<table border="1">'
   Print '<B>' + @TableName + '</B>'
   PRINT '<br>'
   --Get the Description of the table
   --Characters 1-250
   Select substring(cast(Value as varchar(1000)),1,250) FROM 
   sys.extended_properties A
   WHERE A.major_id = OBJECT_ID(@TableName)
   and name = 'MS_Description' and minor_id = 0

   --Characters 251-500
   Select substring(cast(Value as varchar(1000)),251, 250) FROM 
   sys.extended_properties A
   WHERE A.major_id = OBJECT_ID(@TableName)
   and name = 'MS_Description' and minor_id = 0

   PRINT '<tr>'
   --Set up the Column Headers for the Table
   PRINT '<td><b>Column Name</b></td>'
   PRINT '<td><b>Description</b></td>'
   PRINT '<td><b>InPrimaryKey</b></td>'
   PRINT '<td><b>IsForeignKey</b></td>'
   PRINT '<td><b>DataType</b></td>'
   PRINT '<td><b>Length</b></td>'
   PRINT '<td><b>Numeric Precision</b></td>'
   PRINT '<td><b>Numeric Scale</b></td>'
   PRINT '<td><b>Nullable</b></td>'
   PRINT '<td><b>Computed</b></td>'
   PRINT '<td><b>Identity</b></td>'
   PRINT '<td><b>Default Value</b></td>'

   --Get the Table Data
   SELECT 
      '</tr>',
      '<tr>',
      '<td>' + CAST(clmns.name AS VARCHAR(35)) + '</td>',
      '<td>' + substring(ISNULL(CAST(exprop.value AS VARCHAR(255)),''),1,250),
      substring(ISNULL(CAST(exprop.value AS VARCHAR(500)),''),251,250) + '</td>',
      '<td>' + CAST(ISNULL(idxcol.index_column_id, 0)AS VARCHAR(20)) + '</td>',
      '<td>' + CAST(ISNULL(
      (SELECT TOP 1 1
       FROM sys.foreign_key_columns AS fkclmn
       WHERE fkclmn.parent_column_id = clmns.column_id
       AND fkclmn.parent_object_id = clmns.object_id
      ), 0) AS VARCHAR(20)) + '</td>',
      '<td>' + CAST(udt.name AS CHAR(15)) + '</td>' ,
      '<td>' + CAST(CAST(CASE WHEN typ.name IN (N'nchar', N'nvarchar') AND clmns.max_length <> -1
      THEN clmns.max_length/2
      ELSE clmns.max_length END AS INT) AS VARCHAR(20)) + '</td>',
      '<td>' + CAST(CAST(clmns.precision AS INT) AS VARCHAR(20)) + '</td>',
      '<td>' + CAST(CAST(clmns.scale AS INT) AS VARCHAR(20)) + '</td>',
      '<td>' + CAST(clmns.is_nullable AS VARCHAR(20)) + '</td>' ,
      '<td>' + CAST(clmns.is_computed AS VARCHAR(20)) + '</td>' ,
      '<td>' + CAST(clmns.is_identity AS VARCHAR(20)) + '</td>' ,
      '<td>' + isnull(CAST(cnstr.definition AS VARCHAR(20)),'') + '</td>'
   FROM sys.tables AS tbl
   INNER JOIN sys.all_columns AS clmns ON clmns.object_id=tbl.object_id
   LEFT OUTER JOIN sys.indexes AS idx ON idx.object_id = clmns.object_id AND 1 =idx.is_primary_key
   LEFT OUTER JOIN sys.index_columns AS idxcol ON idxcol.index_id = idx.index_id AND idxcol.column_id = clmns.column_id AND idxcol.object_id = clmns.object_id AND 0 = idxcol.is_included_column
   LEFT OUTER JOIN sys.types AS udt ON udt.user_type_id = clmns.user_type_id
   LEFT OUTER JOIN sys.types AS typ ON typ.user_type_id = clmns.system_type_id AND typ.user_type_id = typ.system_type_id
   LEFT JOIN sys.default_constraints AS cnstr ON cnstr.object_id=clmns.default_object_id 
   LEFT OUTER JOIN sys.extended_properties exprop ON exprop.major_id = clmns.object_id AND exprop.minor_id = clmns.column_id
   WHERE (tbl.name = @TableName and exprop.class = 1) --I don't wand to include comments on indexes
   ORDER BY clmns.column_id ASC

   PRINT '</tr></table>'

   FETCH NEXT FROM Tbls INTO @TableName
END

PRINT '</body></HTML>'

CLOSE Tbls
DEALLOCATE Tbls
Next Steps
  • The script can be scheduled to run as a job, so you will never have to worry about updating the documentation manually.
  • Since you are storing the documentation in the database, you never have to worry about having multiple copies and trying to figure out which one is current. It also gets backed up with the database.  So the value is very high.
  • The script will run without adding the extended properties, but obviously you will be missing the descriptions, so take some time and start adding the information to your environment.


Last Update:


next webcast button


next tip button



About the author
MSSQLTips author Ken Simmons Ken Simmons is a database administrator, developer, SQL Server book author and Microsoft SQL Server MVP.

View all my tips





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Friday, March 27, 2015 - 12:43:49 PM - NK Back To Top

Thanks for the tip!!!

What is the "InPrimaryKey" column and why some of the tables return incremented value. Like first column is 1, second column is 2, third column is 3, etc...

 

How do we get the exteneded property for the tables only.


Thursday, October 23, 2014 - 11:10:39 AM - Dona Peschko Back To Top

Very Useful Script ! 

But how can I save it as a TEXT or non-HTML file ?

Thanks 


Wednesday, February 20, 2013 - 5:43:55 AM - Rui Back To Top

hi

how do i get the MS_Description extended propertie from a database ?

thks


Thursday, December 06, 2012 - 3:33:37 PM - Stephen Back To Top

 

Tried this and it works great for the columns in my tables but not for views.  I can assign the Extended Properties to the columns of a view, but the HTML seems to ignore them when generating the data dictionary.  Any suggestions?  Thanks


Wednesday, March 30, 2011 - 7:06:48 PM - GLensegrav Back To Top

Awesome script. Works like a charm. I didn't know about the Extended Properties until I saw this article. Thanks!


Thursday, March 25, 2010 - 2:53:54 PM - Wingenious Back To Top

Dean,

There's a free tool available with a GUI editor for extended properties.

The tool, DBGizmo, also provides some of the features most commonly requested in SQL Server forums.

This handy tool is absolutely FREE, but any feedback would be appreciated.

DBGizmo can be downloaded here... http://www.DBGizmo.net

 


Friday, March 05, 2010 - 8:59:17 AM - acutshall Back To Top

Well, you'd have to create the GUI front end yourself, but to extract the current description from a field call the following (let the column name be NULL to get the table description):

        SELECT @OldDesc = CAST(D.Value AS nvarchar(128))
            FROM ::fn_ListExtendedProperty('MS_Description', 'schema', 'dbo', 'table', @TableName, N'column', @ColName) D;

In order to update a description, you first have to remove the original one (if it exists), then replace it with a new one:

         IF NOT (@OldDesc IS NULL OR @@RowCount = 0)
            EXEC sp_dropextendedproperty N'MS_Description', N'schema', N'dbo', N'table', @TableName, N'column', @ColName;
        EXEC sp_addextendedproperty N'MS_Description', @NewDesc, N'schema', N'dbo', N'table', @TableName, N'column', @ColName;
 

I hope this points you in the right direction.

--Aaron


Friday, March 05, 2010 - 7:42:39 AM - dlgross Back To Top

do you have any suggestions for tools that can be used by end users to maintain the extended properties? I'd like to add the extended properties to the db objects in ssms and then have some type of form that people with domain specific knowledge could use to populate the properties. This is an existing db that I am trying to get documented.

TIA
Dean


Thursday, June 26, 2008 - 12:58:21 PM - dcoleman2 Back To Top
Nice!  Elegant, and the price is right ;) Two tweaks may be useful to others:  Im way behind on column descriptions so moved the exprop.class = 1 from the WHERE to the ON to show columns that lack descriptions. Changed @TableName from v(50) to sysname I got long table names. Im off to create http endpoints to let my servers publish their own DD on demand!

 


Sunday, June 08, 2008 - 10:26:45 PM - amirs Back To Top

thanks for repaly

but i set  the extended property  of some tables in databse  .


Wednesday, June 04, 2008 - 12:11:29 PM - KenSimmons Back To Top

That is becuase you do not have any extended properties set on the tables.


Wednesday, June 04, 2008 - 12:54:43 AM - amirs Back To Top

hi i have use this script of link http://www.mssqltips.com/tip.asp?tip=1499

but i have not dispaly row and colomn of table only dispaly table name


Tuesday, May 27, 2008 - 7:47:18 AM - jerryhung Back To Top

On top of the SSMS Toolbar (the same one that has the red Execute button), there are 3 icons near the right

Results to Text

Results to Grid

Results to File

 

just click on the one you need


Tuesday, May 27, 2008 - 7:37:25 AM - cgw Back To Top

You can go to Tools -> Options -> Query Results -> SQL Server -> General, and change the default destination to "Results to test".

I'm not crazy about changing my results default, but I could not find a command that I could execute as part of the script.


Tuesday, May 27, 2008 - 6:29:13 AM - acutshall Back To Top

How do you specify writing results to text instead of grid?


Thursday, May 15, 2008 - 7:17:34 AM - aprato Back To Top

 Jerry

 Write your results to text instead of grid


Thursday, May 15, 2008 - 6:17:13 AM - jerryhung Back To Top

It gave me the error when I tested it on AdventureWorks

"The query has exceeded the maximum number of result sets that can be displayed in the results grid. Only the first 100 result sets are displayed in the grid."

 

Anyway, I use RedGate's SQL Doc to generate DB docs

http://www.red-gate.com/products/SQL_Doc/index.htm 


Learn more about SQL Server tools