Industrial-strength database documentation using Extended Properties

Question

Industrial-strength database documentation using Extended Properties

Adam Aspin

SSCarpal Tunnel

Points: 4900
More actions
April 5, 2011 at 12:01 am

#237333

Comments posted to this topic are about the item Industrial-strength database documentation using Extended Properties

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Charles Kincaid SSChampion Points: 13593 More actions · Answer 1

Great article and series.

I am curious though. You use "Description" and SSMS for columns uses "MS_Description". Is that because you are intending the article to be a teaching tool and want to avoid stepping on things?

ATBCharles Kincaid

Adam Aspin SSCarpal Tunnel Points: 4900 More actions · Answer 2

Hi Charles,

I am using "Description" for a couple of reasons - firstly to show that you can classify metadata comments by your own categorisation types (I have used DataLineage, ToolTip as well) and secondly because I have noticed that views in SSMS use the MS_ prefix - and this way I can avoid MS-based data when outputting.

I don't know if this is good or bad, approved or not - but it seems to work!

Thanks for the feedback!

Adam

Vedran Kesegic SSCrazy Eights Points: 9302 More actions · Answer 3

XDetails plugin uses MS_Description, as it is standard in SSMS.

_____________________________________________________
Microsoft Certified Master: SQL Server 2008
XDetails Addin - for SQL Developers
blog.sqlxdetails.com - Transaction log myths

Wingenious SSCommitted Points: 1648 More actions · Answer 4

This free tool uses MS_Description as well...

http://www.DBGizmo.net

Creator of SQLFacts, a free suite of tools for SQL Server database professionals.

SQLRNNR SSC Guru Points: 281334 More actions · Answer 5

Thanks for the article. Great content.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

fahey.jonathan Hall of Fame Points: 3617 More actions · Answer 6

Thanks for the great series of articles. I started using the extended properties when I got a tool that would create database documentation based on those properties.

I found that I had many columns with the same name and were really the same item in various tables. For example, many tables had the "AccountID" column, and all were copies of the same thing (primary key in the Account table, foreign key in all other tables). When writing the statement that creates the sp_addextendedproperty, I found it helpful to try to locate another field with the same name and use that as the default value. That allowed me to save a lot of retyping. Of course, I had to review each generated statement for accuracy and make changes as necessary, but it was better than having to look up what I created for the previous tables and retype or copy/paste every time. For my defaults, I used the most-frequently-used description for columns with the same name.

Again, thanks for the thorough explanation and the helpful examples.

Adam Aspin SSCarpal Tunnel Points: 4900 More actions · Answer 7

Hi Jonsathan,

You have good ideas there - cross-referencing columns with the same name and reusing repeatable descriptions. Thanks for sharing that.

Adam

SSIS Guy SSCommitted Points: 1667 More actions · Answer 8

Here is one more Free Tool to document Sql Server

xyzmel SSC Rookie Points: 30 More actions · Answer 9

Very informative series of articles. What would be helpful to me would be the ability to combine results from more than one database. We typically have a pair of databases for an application (data and code), and we have quite a few pairs. It would be nice if there could be a single instance of each stored procedure that could be executed against any database. Is there a way to do that?