Technical Article

Data Dictionary using sys.objects - SQL 2008


I created a script to create a data dictionary in July of 2006 mainly for SQL 2005 and it did work with 2008. However, I realized that it wasn't dynamic enough for a real time data dictionary and had other developers complaining that the dictionary wasn't up to date.

So I created the two stored procedures, code attached and a SQL Server Reporting Service (SSRS) report and when the report is rendered the informationis up to date as of the date the report ran. I'm running the SSRS report using the two stored procedures against a database that has 50 tables and the report rendered within 10 seconds.

Of course the more tables the more time before the report is rendered but at least I won't here that the dictionary is out dated.


So here is what the stored procedures do:

The first stored procedure gets the table name and description, if the description has been added either via the GUI of table design or through the sp_addextendedproperty command.

The second stored procedure provides information on the columns within the table, the information that is returned is the column name, datatype with the length defined, if the column is nullable, default value and the description if added via GUI or sp_addextendedpropery command.


Hope this is helpful to someone.




--- Code for first stored procedure that gets table information ---
--- This will get all the tables in the database and then any values created 
--- in extended properties
t.object_id,,ep.value from sys.tables t
inner join sys.extended_properties ep on ep.major_id = t.object_id and ep.minor_id = t.parent_object_id
where not in ('sysdiagrams','DataDictionary')

--- Code for second stored procedure
--- When the report passes the object_id of the table to the sp
--- this will get all the fields, datatype, is column nullable and default
--- value of field if one is defined.

        , + '(' + case when c.max_length = -1 then 'max' else cast(c.max_length as varchar(100)) end + ')' DataType
        ,    case when c.is_nullable = 0 then 'False' else 'True' end ColumnNullable
        ,    ep.value Descrip
        ,    dc.definition DefaultValue
        ,    c.column_id
        ,    case when c.is_identity = 1 then 'Yes' else '' end PK
             from sys.columns c
        inner join sys.systypes st on st.xtype = c.user_type_id
        left outer join sys.extended_properties ep on ep.major_id = c.object_id and ep.minor_id = c.column_id
        left outer join sys.default_constraints dc on dc.parent_column_id = c.column_id and dc.parent_object_id = c.object_id
      where c.object_id = @ObjectID
      order by c.column_id