Data Dictionary from within SQL

Server 2000
by Mindy Curnutt
©2002 Unisys Corporation

There is

functionality within SQL Server 2000 that allows you to enter metadata

on the columns in the SQL Server. In fact, the 'Description' field (shown below)

is a meta data field that comes right with SQL Server. This functionality can be used to

create a traditional "Data Dictionary" - allowing you to define the purpose of each column and then to list all

the tables and columns (with their fully described purpose and meaning) in a Dictionary type fashion.  The

difficulty is generally not easy to populate or query this data. 

To populate the Description Meta Data field for a

column is actually not too difficult if you use the Enterprise Manager

GUI.  Here is a screen shot of where you do this:

These values are stored

as extended properties. The user can add as many extended properties as they like. They

are stored in a hidden table that you will not see in any database

on the SQL Server. 

You could also manually assign this description

through Query Analyzer:

DECLARE @v sql_variant

SET @v = N'Type of Email Addr -

Home, Work, etc...'
EXECUTE

sp_updateextendedproperty N'MS_Description', @v, N'user',
N'dbo', N'table', N'tbl_email_type', N'column',

N'email_type_name'

You can then use

the following type of query to return a table, it's columns in order, and any

entered Description:

declare @table_name varchar(128)

set @table_name = N'tbl_email_type'

select
o.[id] as 'table_id',

o.[name] as 'table_name',
c.colorder as 'column_order',
c.[name] as

'column_name',
e.value as 'column_description'

from sysobjects o inner join

syscolumns c on o.id = c.id
left join

::FN_LISTEXTENDEDPROPERTY(N'MS_Description',
N'user',N'dbo',N'table',@table_name,

N'column', null) e on c.name = e.objname
where o.name =

@table_name
order by c.colorder

This returns a resultset that looks like

this:

If you want to show all the tables, you

can make the above code into a stored procedure and step through the tables using a

cursor, sending each one to the stored procedure.

Create the stored procedure:

create

procedure get_column_details @table_name

nvarchar(128)
as
 
select
o.[id] as 'table_id',
o.[name]

as 'table_name',
c.colorder as 'column_order',
c.[name] as

'column_name',
e.value as 'column_description' 

from sysobjects o inner join syscolumns c on

o.id = c.id
left join

::FN_LISTEXTENDEDPROPERTY(N'MS_Description',
N'user',N'dbo',N'table', @table_name,

N'column', null) e on c.name = e.objname
where o.name =

@table_name
order by c.colorder

Call the stored

procedure for each table.

DECLARE @table_name nvarchar(128)

DECLARE tablenames_cursor CURSOR FOR 
SELECT name FROM sysobjects where type =

'U' and status > 1 order by name

OPEN tablenames_cursor
FETCH

NEXT FROM tablenames_cursor INTO @table_name
WHILE @@FETCH_STATUS =

0
BEGIN
   exec get_column_details @table_name
  

FETCH NEXT FROM tablenames_cursor INTO @table_name
END

CLOSE

tablenames_cursor
DEALLOCATE tablenames_cursor

It would have

been easier to use the undocumented sp_MSforeachtable - but that returns each table name as

[dbo].[tablename] and the brackets and the table owner name do not work with the

::FN_LISTEXTENDEDPROPERTY call.

🙁