Do you generate your data dictionary? [closed]

Question

Do you generate your data dictionary? If so, how?

I use extended procedures in SQL Server 2005 to hold onto table and field information. I have some queries that create a dictionary out of them, but it's ... meh. Do you have a particular query or tool you use? Do you generate it off of your database diagrams?

Googling for "data dictionary sql server" comes up with many queries, but they're all about equally as attractive. Which is to say, good starting off points, but not production ready.

Answer 1

Recently I had a task to document a fairly large database (around 500 objects) and details I found here really helped.

Here is some feedback on how we implemented this – hopefully someone will find it useful even though it’s quite late.

Technique:

• Extended properties in all objects + third party tool to generate docs

What was documented:

• All tables and some columns (we added good descriptions for all tables to really make sure it’s clear what the table is about)

• All views – descriptions on why the view was created, what tables are included in the data and such and also when to use

• All stored procedures – while going through the process we found that we had a lot of duplicate stored procedures ( devs didn’t bother to see if proc exists so they created new ones )

• All UDFs and some other objects as well but not all (we didn’t really have the need to document triggers)

What we ended up doing is having our DBA to reject all DDL updates that come from devs unless there are extended properties.

We also have a scheduled task to automatically re-create documentation every 2 weeks.

Answer 2

We use extended properties.

To read them, we use sys.extended_properties It makes things a lot easier.

We also use Red Gate SQL Doc