Developer.com content and product recommendations are editorially independent. We may make money when you click on links to our partners. Learn More.
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
@name=N'MS_Description',
@value=N'The employees database contains all information related
to an employee of ABC company.'
GO
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]
GO
--My Extended Properties script
Add Extended Properties...
...
BEGIN TRY
EXEC sys.sp_dropextendedproperty @name=N'MS_Description',
@level0type=N'SCHEMA',
@level0name=N'dbo',
@level1type=N'TABLE',
@level1name=N'dept',
@level2type=N'COLUMN',
@level2name=N'dept_id'
END TRY
--Catch error if there is no property to drop, then continue...
BEGIN CATCH END CATCH
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...
GO
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.
