Expand Your SQL Server 2005 Dictionary Using Extended Properties

Tuesday May 15th 2007 by Michael Klaene

Generate a data dictionary using SQL Server 2005 metadata then take it one step further. Use Extended Properties to add additional metadata that will explain why each data object is important to the application that uses it.

There are a number of ways to obtain metadata in SQL Server 2005. Metadata, in the simplest terms, means data about data. Metadata is content that describes your application's data and potentially how that data is used. Metadata is useful in many ways. Software vendors rely extensively on metdata when building database tools. Application developers can utilize metadata for tasks such as constructing dynamic SQL statements. By combining metadata available in SQL Server system views with SQL Server Extended Properties, you can compile a rich data dictionary for all interested parties.

SQL Server 2005 provides access to a standard set of metadata through a collection of system 'catalog views' as well as a set of views under a new schema called 'INFORMATION_SCHEMA'. Although information such as a column's data type and length is most certainly useful, SQL Server is unable to infer how these database objects are applicable to the applications that use them. This is where Extended Properties come in. Extended Properties allow you to add additional metadata to explain why each data object is important to the application that uses it. Actually, Extended Properties have many uses and you can learn about them here. In the meantime, you will focus on how developers can use Extended Properties to document, not only tables and views, but also database programs as well. For example, Extended Properties can be helpful when documenting code, similar to how a C# developer might use a tools such as NDoc to document his or her code.

There are a few different ways to work with Extended Properties. In SQL Server Management Studio, right-clicking on a column, selecting Properties, and then Extended Properties from the list on the left will display the Extended Properties for that column. You also can call a set of procedures directly, like the following example that adds a brief description about the database itself.

-- Add an extended property to a database
EXEC sp_addextendedproperty
   @value=N'The employees database contains all information related
            to an employee of ABC company.'

The first thing that comes to mind when adding Extended Properties to an object is to provide a basic description of that object. In fact, if you enter text in the 'Description' property of a column in Management Studio, you are creating an Extended Property called 'MS_Description'. It is usually a good idea to be concise with these descriptions. It will make it easier to read and easier to maintain over the life of the database.

Here is a query that will display some key properties for columns on the table 'emp':

SELECT isc.column_name , isc.is_nullable,
       isc.data_type, isc.character_maximum_length,
       c.object_id, c.column_id
   FROM information_schema.columns isc
   INNER JOIN sys.columns c ON isc.column_name = c.name
   AND OBJECT_NAME(c.object_id) = 'emp'
   AND isc.table_name = 'emp'
   AND OBJECTPROPERTY(c.object_id, 'IsMsShipped')=0

You can follow this query with additional queries to retreive Extended Properties. Here, you display the Extended Properties for the 'first_name' column using the object's 'object_id':

SELECT CAST(ex.name  AS VARCHAR(1000)) AS ext_prop_name,
   CAST(ex.value AS VARCHAR(1000)) AS ext_prop_value
   FROM  (
      SELECT c.object_id, c.column_id
         FROM information_schema.columns isc
         INNER JOIN sys.columns c ON isc.column_name = c.name
         AND OBJECT_NAME(c.object_id) = 'emp'
         AND isc.table_name = 'emp'
         AND isc.column_name = 'first_name'
   ) cols
   LEFT OUTER JOIN  sys.extended_properties ex
   ON  ex.major_id = cols.object_id
   AND ex.minor_id = cols.column_id

Here is a subset of the data returned by the first query:

column_name   is_nullable    data_type   character_maximum_length
 emp_id           NO          bigint           NULL
 first_name       NO          varchar           50
 last_name        NO          varchar           50
 dept_id          NO          bigint           NULL

And the second query...

MS_Description   First name of employee.

You can add as many extended properties as you want. For example, you might include an Extended Property called 'Input Screen' to identify where a table's data is displayed and modified by end users. Because this content is valuable in understanding an application, it may be a good idea to consider creating an Extended Properties script and placing it in source control along with other application objects.

USE [employees]
--My Extended Properties script

Add Extended Properties...


   EXEC sys.sp_dropextendedproperty @name=N'MS_Description',
--Catch error if there is no property to drop, then continue...

EXEC sys.sp_addextendedproperty @name=N'MS_Description',
   @value=N'Unique identifier for the dept table.' ,
   @level0type=N'SCHEMA', @level0name=N'dbo',
   @level1type=N'TABLE',  @level1name=N'dept',
   @level2type=N'COLUMN', @level2name=N'dept_id'


More Extended Properties...


Here is a script that loops through database objects and generates a dictionary of tables, views, and programs (stored procedures, functions, triggers). Using cursors, it drills down and extracts this data in an easy-to-read manner. For example, it starts with a table, displays all of the tables' Extended Properties, then obtains the tables' columns, and finally displays each Extended Property for that column as well as a select set of standard properties such as data type and length. It will print out this information in HTML format that you can export to a file. The HTML is not the prettiest, but this script can be can be a good starting point for a robust data dictionary.

About the Author

Michael Klaene is a Senior Consultant with Sogeti LLC. He has spent over 9 years in Information Technology and is an experienced Systems Analyst, delivering solutions that involve numerous technologies, such as J2EE and .NET.

Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved