Create Database Schema Documentation

StrataFrame Forum

Back

Create Database Schema Documentation

Author	Message
Jason Seidell	Jason Seidell posted 14 Years Ago Topic Details Share Topic
Group: Forum Members Posts: 59, Visits: 180	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 Jason Seidell Programmer/Analyst Infinedi
	Reply Like 1
Ivan George Borges	Ivan George Borges posted 14 Years Ago Post Details Share Post
Group: StrataFrame MVPs Posts: 1.9K, Visits: 21K	Thanks for sharing, Jason.
	Reply Like 1
Keith Chisarik	Keith Chisarik posted 14 Years Ago Post Details Share Post
Group: StrataFrame Users Posts: 939, Visits: 40K	You can also look in the XML files in the DDT package file for this info. Keith Chisarik Sigma Data Systems
	Reply Like 1
Jason Seidell	Jason Seidell posted 14 Years Ago Post Details Share Post
Group: Forum Members Posts: 59, Visits: 180	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 Jason Seidell Programmer/Analyst Infinedi
	Reply Like 1