Conrad Muller
Seattle, Washington

Email: conrad at
dot com

Start Database Documenting with T-SQL Scripts

This article describes scripts that automatically generate descriptions for some tyoes of database objects. We begin by contrasting scripts that must be manually completed with scripts which generate the descriptions automatically.

Manually adding or updating descriptions of Tables

AddTableDescriptions.sql will list all of the tables in your database that do not have object descriptions. The output is a set of SQL statements which allow you to add the description text and execute the statements to add the descriptions to the tables. This script only works if you set "Results To" to "Results to Text" using the Right-Click context menu.

The output of this query will have 'xxx' as a place holder for the new column description. Replace the "xxx" with your description of the column, and run the script against the database.

The output of the AddTableDescriptions.sql script looks like this:

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value= N'xxx', @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DayOfWeek'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value= N'xxx', @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'HolidayCategory'

As you can see, we are saving @value xxx to @name MS_Description for the table dbo.DayOfWeek. Of course we will replace xxx with a description before we run the query.

Automatically adding or updating descriptions of Constraints

Even a modest database can have hundreds of database objects, so I have created some T-SQL scripts which generate descriptions automatically, based on data extracted from the system tables.

AddTableConstraintAutoDescriptions.sql produces T-SQL scripts with the description already filled in.

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'This is a Default constraint on TABLE = Account, COLUMN = isActive, with default = ((1)).', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'Account', @level2type=N'CONSTRAINT', @level2name=N'DF_Account_isActive'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'This is a Default constraint on TABLE = Account, COLUMN = CreatedByAccountID, with default = ((1)).', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'Account', @level2type=N'CONSTRAINT', @level2name=N'DF_Account_CreatedByAccount'

As you can see, we are saving @value "This is a Default constraint on TABLE = Account, COLUMN = isActive, with default = ((1))." to @name MS_Description for the constraint dbo.Account.DF_Account_isActive.

The scripts which can semi-automatically document database objects are:

Each script has a section at the top which briefly explains what it does and how to use it.

There is one script that is a bit different. AddTableColumnAutoDescriptions.sql

This script will automatically generate scripts to add or update descriptions for 8 columns which are commonly included in the tables in any database I design. They include:

The script finds these columns wherever they occur in the database, and adds or updates the description. This script is written for my own commonly used column names, so it may not work for you without being updated. It should not be a problem to change the target column name and the description to make this script work for the commonly used columns in your own database designs. 

This page is licensed under a Creative Commons Attribution-NonCommercial-NoDerivs 2.5 License by Conrad Muller.

These scripts are intended for education and are provided "AS IS"without warranty of any kind, either expressed or implied, including but not limited to implied warranties of merchantability and/or fitness for a particular purpose.

Home | Resume | Project Portfolio | Writings | Developer Resources | Contact Form