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'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value= N'xxx', @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'HolidayCategory'
GO
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'
GO
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'
GO
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:
- AddTableConstraintAutoDescriptions.sql This script generates queries which document Constraints, such as Default values.
- AddUpdatePKColumnAutoDescriptions.sql This script generates queries which document Primary Keys.
- AddUpdateFKAutoDescriptions This script generates queries which document Foreign Keys.
- AddTableIndexAutoDescriptions.sql This script generates queries which document Indexes.
- AddTableTriggerAutoDescriptions.sql This script generates queries which document Triggers.
- Add_FN_SP_ParameterAutoDescriptions This script generates queries which document User Defined Functions and Stored Procedures.
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:
- Name
- Note
- DisplayOrder - This column is used to provide user defined ordering for lookup tables.
- isActive
- CreatedByAccountID
- CreatedDate
- ModifiedByAccountID
- ModifiedDate.
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.