SQL Create Table Add Description to Column

One nice way to build "documentation" directly into your database, you can add extended properties to your tables to describe the columns. This is useful because this data can be displayed in your database diagram.

NOTE: This has only been tested in SQL Server 2005 and 2008.

Normally, to display / manage the descriptions of your columns, you use the SQL Database Diagram tool. Here's what my Database Diagram for my simple database. By default, the "description" field is not displayed.

To show the description field, you need to edit your custom view for the table. Select all your tables on the diagram and click Table View -> Modify Custom...

In the dialog, choose the columns you want to display. Find the "Description" field and add it to the list.

Now change the default view for your tables to "Custom" by going to Table View -> Custom. This will change all tables you have higlighted to show the description field.

Now you can see the description and you can add / update it directly in the table window.

If you wanted to do this in SQL code instead, here is the SQL. You can get a list of the current description properties for a column by using the built-in SQL function fn_listextendedproperty. Here is an example of the syntax. This example returns all description properties for the table 'my_table_name'.

-- fn_listextendedproperty - parameters:

-- Name: The name of the property (NULL gets all names). Ex: 'MS_Description' is the property name for "description" in SQL Server.

-- Level0Type: The top level object type to search from. Ex: 'schema' is the type of object since we are reading from the schema of the DB.

-- Level0Name: The top level object name to search from. Ex: 'dbo' is the name of the schema to read from.

-- Level1Type: The second level object type. Ex: 'table' is used because we want to get properties for a table in the dbo.

-- Level1Name: The second level object name. Ex: 'my_table_name' is the name of the table to read properties from.

-- Level2Type: The third level object type. Ex: 'column' specifies that we want the properties for the column type of the table.

-- Level2Name: The third level object name (NULL gets all names). Ex: null is here instead of a column name to return ALL columns for the table.

select objname, [name], [value] from fn_listextendedproperty (

'MS_Description', 

'schema', 

'dbo', 

'table', 

'my_table_name', 

'column', 

null)

In this case, 'MS_Description' is the name of the extended property SQL Server uses for the description field on a column/table. 'schema' is the top level object, 'dbo' is the top level object name, 'table' is specified as the second level type because we want to get extended properties for a table, 'my_table_name' is the name of the table we want to get properties for, 'column' specifies that we want to get the properties for the columns in the table, and 'null' is specified for the table column name so that it returns the properties for all columns in the table.

Here are more details for fn_listextendedproperty function are here:

http://msdn.microsoft.com/en-us/library/ms180047.aspx

To add an extended property to a column in the table, you can use the sp_addextendedproperty built-in SQL stored procedure. Here is an example that adds a description to the my_table_name table's "id" column:

-- add description extended property to the my_table_name table's 'id' column

exec sp_addextendedproperty  

'MS_Description', 

'This is my description for the column',

'schema', 

'dbo', 

'table', 

'my_table_name', 

'column', 

'id'

More details on the sp_addextendedproperty stored procedure are here:

http://msdn.microsoft.com/en-us/library/ms180047.aspx

To update this extended property after it has been added, you can use the sp_updateextendedproperty SQL built-in stored procedure. In this example, we update the property we just added with a new description instead.

-- update description extended property to the 'my_table_name' table's 'id' column

exec sp_updateextendedproperty  

'MS_Description', 

'This is my updated description',

'schema', 

'dbo', 

'table', 

'my_table_name', 

'column', 

'id'

More details on the sp_updateextendedproperty stored procedure are here:

http://msdn.microsoft.com/en-us/library/ms186885.aspx

To drop an extended property, use the sp_dropextendedproperty stored procedure. In this example, we drop the property we just added/updated:

-- drop the description extended property for 'my_table_name' 'id' column

exec sp_dropextendedproperty  

'MS_Description', 

'schema', 

'dbo', 

'table', 

'my_table_name', 

'column', 

'id'

To combine all of this, when creating a new table, just added this as part of your process. Then when you forget what a column is for or why you need it / created it, you can refer to your own descriptions and your SQL and database itself become self-documenting.