StrataFrame Forum

Create Database Schema Documentation

http://forum.strataframe.net/Topic27731.aspx

By Jason Seidell - 7/26/2010

Ok so this is more of an informational post than a question.  I am working to document my latest deployment into Sharepoint for my development team, when I was hit, "Why am I typing a list of tables and columns with descriptions when I already did that in the DDT?"  So the next question became, how do I get that information.  This is another example of M$ creating a new, whiz-bang way of doing things, followed by not-documenting it, and then deprecating it.

The skinny is you can easily find a list of tables (sys.Tables) and columns (sys.Columns) but the descriptions are saved to a seperate extended properties table (sys.extended_properties).  I could not find where or how to get the descriptions until I came across this helpful post, http://databases.aspfaq.com/schema-tutorials/schema-how-do-i-show-the-description-property-of-a-column.html.  Most of the posts either referenced a sysproperties table (which is now deprecated) or a nearly un-usable sproc fn_listextendedproperty.  Anyways here is the SQL queries, which allow a quick dump of tables and columns followed by the descriptions that were put into the DDT.

-- Create a list and description of all tables

SELECT

t1.name,

t2.value

FROM

sys.Tables as t1

LEFT OUTER JOIN

sys.extended_properties as t2

ON

t1.object_id=t2.major_id AND

t2.minor_id=0 AND

t2.name='MS_Description'

ORDER BY t1.name;

-- Create list of all table column names and descriptions

SELECT

[Table Name] = OBJECT_NAME(c.object_id),

[Column Name] = c.name,

[Description] = ex.value

FROM

sys.columns c

LEFT OUTER JOIN

sys.extended_properties ex

ON

ex.major_id = c.object_id

AND ex.minor_id = c.column_id

AND ex.name = 'MS_Description'

WHERE

OBJECTPROPERTY(c.object_id, 'IsMsShipped')=0

-- AND OBJECT_NAME(c.object_id) = 'your_table'

ORDER

BY OBJECT_NAME(c.object_id), c.column_id

Hope someone else finds this useful!!

Jason Seidell

By Ivan George Borges - 7/26/2010

Thanks for sharing, Jason. Wink
By Keith Chisarik - 7/26/2010

You can also look in the XML files in the DDT package file for this info.
By Jason Seidell - 7/26/2010

Keith Chisarik (07/26/2010)
You can also look in the XML files in the DDT package file for this info.

That would be great for a more programmatic access, but I was just looking for a quick and dirty copy and paste action.  However that does create an interesting idea for code that would automatically parse those xml files and then update a documentation source.  So as SF DBA/developer I just need to update my DDT and deploy, without having to merge those changes into the documentation.

Jason