Create Database Schema Documentation

Jason Seidell — Mon, 26 Jul 2010 16:39:19 GMT

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.

[codesnippet]

-- 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

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

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

[/codesnippet]

Hope someone else finds this useful!!

Jason Seidell

RE: Create Database Schema Documentation

Jason Seidell — Mon, 26 Jul 2010 16:39:19 GMT

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

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

RE: Create Database Schema Documentation

Keith Chisarik — Mon, 26 Jul 2010 16:21:29 GMT

You can also look in the XML files in the DDT package file for this info.

RE: Create Database Schema Documentation

Ivan George Borges — Mon, 26 Jul 2010 15:32:09 GMT

Thanks for sharing, Jason. ;)

StrataFrame Forum » StrataFrame Database Deployment Toolkit » Database Deployment Toolkit (How do I?) » Create Database Schema Documentation

Create Database Schema Documentation

RE: Create Database Schema Documentation

RE: Create Database Schema Documentation

RE: Create Database Schema Documentation