What are the Best Practices to follow while creating a data-dictionary?

Question

I have large and complex SQL Server 2005 DB used by multiple applications. I want to create a data-dictionary for maintaining not only my DB objects but also cross-reference them against applications that use a specific object.

For example, if a stored procedure is used by 15 diffrent applications I want to record that additional data too.

What are the key elements to be kept in mind so that I get a efficient and scalable Data Dictionary?

Answer 1

I've always been a fan of using the 'extended properties' within SQL Server for storing this kind of meta data. In this way the description of each object lives alongside the object and is accessible by anyone with access to the database itself. I'm sure there are also tools out there that can read these extended properties and turn them into a nicely formatted document.

As far as being "scalable", I don't know of any issues related to adding large amounts of data as extended properties; or I should say I've never had any issues with this.

You can set these extended properties using SQL Server Management Studio 'property' dialog for each table/proc/function/etc and can also use the 'sp_addextendedproperty'.

Answer 2

So, I recently helped to build a data dictionary for a very large product. We were dealing with documenting more than one-thousand tables using a change request process. I can send you a scrubbed version of the spreadsheet we used if you want. Basically, we captured the following:

• Column Name
• Data Type
• Length
• Scale (for decimals)
• Whether the column is custom for the application(s) or a default column
• Which application(s)/component(s) the column is used in
• Release the column was introduced in
• Business definition

We also captured information about who requested the addition, their contact information, etc. Our primary focus was on business definition, and clearly identifying why a column was being used or created.

We didn't have stored procedures in our solution, but bear in mind that these would be pretty easy to add to the system.

We used Access for our front-end, even though SQL Server was on the back end. It made it pretty easy for us to build out a rich user interface without much work, using the schema we had already built out.

Hope this helps you get started--feel free to ask if you have additional questions.