What are the best practices to follow when creating a data dictionary?


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?

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.