Documentation - Is it good to use the extended properties as data dictionary?

Question

Post reply

Documentation - Is it good to use the extended properties as data dictionary?

DBA Cabuloso

SSCommitted

Points: 1733
More actions
February 11, 2010 at 11:50 am

#216748

Hello friends,
In my company we use SQL 2005 and we don't use any CASE tools. I am studying a new way to document the database objects and I found that there are extended properties that may show up even in the diagrams. But I have never seen people using this properties as documentation.
Is there any problem of performance or any other problem in using these fields as data documentation? Is there any tool that get reports from a database populated with extended properties in the table and in the columns?
Thanks in advance
________________
DBA Cabuloso
Lucas Benevides

Viewing 5 posts - 1 through 4 (of 4 total)

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

SQLRNNR SSC Guru Points: 281334 More actions · Answer 1

I have seen examples of people doing it. They build a pretty good case for it and without performance problems.

I don't do it that way myself.

The answer to the question is - It depends. If you can make it work for you and have it pass policy for your employer. Then it could work well for you.

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

Ron McCullough SSC Guru Points: 63877 More actions · Answer 2

Have used extended properties, and it works well, if your description is well written. If your description of what table is and why it exists, and what each column represents is complete and accurate I consider it a good documentation tool - but not the only one. Also script out entire db to a file and store that file in source safe / printed copy saved in location known to other db developers etc.

And once done make sure both properties/printed docs are kept up to date when changes / additions are made.

For stored procedures use a comment area at the start of each SP as to date, author and a brief description of what the procedure does.

Again each time a SP is altered that section must be kept up to date.

It is all too easy to have people make changes, not change extended properties / SP comment area, seems people always say I have to get this done fast, I will go back and do the updating -- and needles to say it never does get done.

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

SQLRNNR SSC Guru Points: 281334 More actions · Answer 3

Thanks Ron for posting that info.

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

davidr@uslfinancials.com Valued Member Points: 62 More actions · Answer 4

I had the same question recently. With a little digging I found an open source application to use the extended properties to store the data dictionary, and script it so it can be copied /backed up. Also I have a SQL function that formats the Data Dictionary for reporting.

Send me your email address in a PM and I'll send you the the programs.

David Richardson

Public-Sector Solutions