What is the best way to create an enhanced Data Dictionary?

Question

Without going into specifics...I have a large SQlServer 2005 database with umpteen stored-procedures. I have multiple applications from WinForm apps to WebServices all of which use this DB.

My simple objective now is to create a meta-database...a prospective data-dictionary where I can maintain details of which specific app. file uses which SP.

For example, My application Alpha which has a file Beta.aspx...uses 3 SPs which are physically configured for usage in BetaDAL.cs

You might have inferred by now,it will make life easier for me later when there is a migration or deprecation....where I can just query this DB SP-wise to get all Apps/Files that use the DB or vice-versa.

I can establish this as a single de-normalized table..or structure it in a better way.

Does some schema already exist for this purpose?

Answer 1

SQL Server supports what are called extended properties, basically a key-value dictionary attached to every object in the catalog. You can add whatever custom information about the catalog (comments on tables, columns, stored procedures, ...) you wish to store as extended properties and query them along with the normal catalog views.

Here's one overview (written for SQL Server 2005, but roughly the same techniques should apply for 2000 or 2008).