An automated SQL Server Data Dictionary

First, a disclaimer: You’re welcome to use any or all of the scripts, web pages, stored procedures and other software described in this article, but you do so at your own risk. You should also know that the SQL Server system tables and master database are subject to change at Microsoft’s whim and it may be that, though they work on version of SQL Server that I’m currently using (SQL Server 2000), they may not work on other releases of SQL Server, or even after the application of new service packs.

What’s a data dictionary and how can it help me?

A data dictionary contains data about the tables, stored procedures and views in your database. In particular, it provides a place where you can enter a long description of each of the objects in your database.

If you’ve ever created a table in a database, and then forgotten what the table was for, or had to spend hours explaining to another developer why every field in every table was there and what it did, then a data dictionary can help you out.

Is SQLAutoDoc for me?

SQLAutoDoc is intended for small-to-medium-sized organizations. It can span multiple servers, but in larger organizations the security compromises necessary to enable this functionality might be frowned upon by the database administration staff.

It also is not a competetor to comericial database management products such as Computer Associates’ Erwin. If you don’t have Erwin, if your database documentation has become out of date after years of successive modification, or if you’re so busy responding to user change requests that you never find time to document your database, then you might find SQLAutoDoc useful.

Using SQLAutoDoc to view your database dictionary

Once the SQLAutoDoc software is installed on your SQL Server and your web server, then you would use SQLAutoDoc by first navigating to a list of databases, perhaps similar to the screen shown below.

From this screen, you update the descriptions of each of the databases on your server, or you can drill into the database dictionary in any one of five ways.

If you click on the magnifying glass in the “logins” column for a database, you’ll see a listing of the logins in that database, similar to this:

From this screen, as well as the tables, views and stored procedure listings, you can click on the hyperlinked name of the database (in this case, “apphistory”) to return to the database listing.

If you click on the tables, views or stored procedures links, you’ll see a screen that looks much like this:

From the tables, views or stored procedure listing, you can then drill even further down to a screen that shows the column names and data types for tables and views, and the parameter names and types for stored procedures.

As shown in this example, the parameter listing (for stored procedures) and column listing (for views) also includes, where possible, the text of the stored procedure or view definition.

Using the dictionary search function

The final way to view information in your database dictionary is to use the SQLAutoDoc search function. To use the search function, click on the “search” hyperlink at the top of a screen. When you do, you’ll see the search query screen. It looks like this.

To use the search feature, fill in the fields on the search form to narrow down your search. The search feature will find all database objects containing the text that you enter. If you wish to find only objects of a certain type, check the restriction fields to indicate which types of objects you would like to search. To have results returned, you must check at least one of the “include” boxes.

Once you’ve entered your criteria, click on the “search” button to perform the search. The “Reset” button clears all fields on the form.

Here’s an example of a search for all tables that contain a column named “vcAppCode” in the “AppHistory” database.

Updating the dictionary

While a web interface to your SQL Server schema might be somewhat useful, how useful can a database dictionary truly be without definitions? SQL AutoDoc provides a means to associate descriptive text with many database objects.

You can add a description to each of the following types of objects: Databases, logins, tables, stored procedures, views, table fields, stored procedure parameters, and view fields.

Each object type that supports descriptive text has an “update” hyperlink in the upper right-hand corner of the menu. If you click on the link, you’ll see a special “update” screen for that object. Here, for example, is what you would see if you chose to update table descriptions for the tables in the AppHistory database:

Enter the description of the table in the space provided beneath the table. To save your changes, click on the “save changes” button. Couldn’t be simplier, could it?

How does it work?

If you’re considering using SQLAutoDoc, you should be either a database administrator or an advanced developer that’s familiar with SQL Server and Transact SQL. If you’re either and you’ve made it this far, then you’re probably wondering how SQLAutoDoc works.

The heart of SQLAutoDoc is a stored procedure that you schedule to run periodically. This stored procedure scans through each database on the current server. In each database, it compares schema for tables, stored procedures and views against a metadata database. If changes are detected, then the metadata database is updated.

Each record in the metadata database also contains a description field where a description of the object can be recorded. These description fields are updated via a web form.

The entire schema can also be navigated using web reports.

SQLAutoDoc consists of these tables, located in a database that must be named “METADATA”:

tDDDatabases	A list of databases located on this server. The “master”, “model”, “msdb”, “NorthWind”, “pubs” and “tempdb” databases are explicitly excluded from SQLAutoDoc monitoring.
tDDTables	A list of tables
tDDFields	A list of table fields, with description, type of data contained, order and other field-related information.
tDDSProcs	A list of stored procedures
tDDParams	A list of stored procedure parameters, with description, type of data contained, and order.
tDDSProcText	The text of all non-encrypted stored procedures
tDDViews	A list of views
tDDVFields	A list of view fields, with description, type of data contained, order and other field-related information.
tDDViewText	The text of all non-encrypted views
tDDLogins	A list of database logins

 

Here’s a diagram of how the tables in the METADATA database relate to each other.

The tables are populated by stored procedures. Two of these stored procedures, sp_DDScanForDatabases and sp_DDScanForTables, must be located in the server’s MASTER database.

sp_DDScanForDatabases is scheduled to run periodically (once per day, perhaps?). This stored procedure scans through the server’s sysdatabases table. For each database detected, it then executes the sp_DDScanForTables stored procedure.

Deleted databases are detected by first marking all database records in the tDDDatabases table as deleted. As the stored procedure finds databases, the tDDDatabases table is examined. If a record for the database is found to already exist, then the “deleted” flag is cleared. When the stored procedure is finished executing, only those databases without matching records in the sysdatabases table are left flagged as deleted.

It is the sp_DDScanForTables stored procedure that actually does most of the work. This stored procedure scans various system tables including the sysusers table for logins, and the sysobjects table for tables, views and stored procedures. For each object that it finds, it executes an appropriate stored procedure in the METADATA database. This stored procedure, in turn, looks to see if a record already exists for the object. If a record does not already exist for the object, then one is created for it.

Deleted tables, views and stored procedures are detected in the same fashion as deleted databases with the exception that not only are all of the tables, views and stored procedures initially marked as deleted, but so are the table fields, view fields and stored procedure parameters. Then, as each is determined to still exist, its “deleted” flag is cleared.

Here’s a list of stored procedures used by SQLAutoDoc, and where they must be located:

In the MASTER database:

sp_DDScanForDatabases	Scans all user databases on the current server looking for new or changed databases, tables, views or stored procedures.
sp_DDScanForTables	Used by sp_DDScanForDatabases to scan the contents of a single database for changed tables, views or stored procedures.

In the METADATA database:

sp_DDAddDatabase	Adds a new database to the database, or clears the “deleted” flag for the database if it already exists.
sp_DDAddField	Adds a new table field to the database, or clears the “deleted” flag for the field if it already exists.
sp_DDAddLogin	Adds a new user login to the database, or clears the “deleted” flag for the login if it already exists.
sp_DDAddParam	Adds a new stored procedure parameter to the database, or clears the “deleted” flag for the parameter if it already exists.
sp_DDAddSProc	Adds a new stored procedure to the database, or clears the “deleted” flag for the stored procedure if it already exists.
sp_DDAddTable	Adds a new table to the database, or clears the “deleted” flag for the table if it already exists.
sp_DDAddVField	Adds a new view field to the database, or clears the “deleted” flag for the field if it already exists.
sp_DDAddView	Adds a new view to the database, or clears the “deleted” flag for the view if it already exists.
sp_DDMarkDatabasesDeleted	Sets the “deleted” flag for all records in the tDDDatabases table.
sp_DDMarkTablesDeleted	Sets the “deleted” flag for all records in the tDDTables, tDDVFields and tDDSProcs tables.

The SQLAutoDoc interface

I’ve created a set of simple web reports as an interface to SQLAutoDoc. The web reports can be used to update the descriptions for various database objects, to list database objects, or to search for objects by name and/or attribute.

The web reports use ASP and have been tested on IIS v5.0 (the IIS that is included as part of Windows 2000 server).

Here’s a list of the reports and a short description of each report’s function:

DDDatabases.asp	Lists all of the databases on a server. For each listed database, a description is shown if one exists. Also shown are the path to the database’s MDF file and links to lists of tables, views and stored procedures contained in the database.
UDDatabases.asp	Allows you to enter a short description of the function of each database on the server.
DDFind.asp	Allows you to search the MetaData database for entities containing a character string that you provide.
DDLogins.asp	Lists all logins in the database and indicates for each login whether or not it uses integrated security.
UDLogins.asp	Allows you to enter a short description of the function of each login in a database.
DDTables.asp	Lists all of the tables in a database. Also shown are the table owner, the description of the table, if one has been provided, and the date that the table was discovered by SQLAutoDoc.
UDTables.asp	Allows you to enter a short description of the function of each table in a specified database.
DDViews.asp	Lists all of the views in a database. Also shown are the view owner, the description of the view, if one has been provided, and the date that the view was discovered by SQLAutoDoc.
UDViews.asp	Allows you to enter a short description of the function of each view in a specified database.
DDSProcs.asp	Lists all of the stored procedures in a database. Also shown are the stored procedure owner, the description of the stored procedure, if one has been provided, and the date that the stored procedure was discovered by SQLAutoDoc.
UDSProcs.asp	Allows you to enter a short description of the function of each view in a specified database.
DDFields.asp	Lists all of the fields in a table, the data type of each field and whether the field is nullable. If a description has been provided then it is also shown.
UDFields.asp	Allows you to enter a short description of the function of each column in a specified table.
DDVFields.asp	Lists all of the fields in a view, the data type of each field and whether the field is nullable. If a description has been provided then it is also shown. Also shows the text of the view, if it is available.
UDVFields.asp	Allows you to enter a short description of the function of each column in a specified view.
DDParams.asp	Lists all of the parameters for a stored procedure and the data type for each parameter. If a description has been provided then it is also shown. Also shows the text of the stored procedure, if it is available.
UDParams.asp	Allows you to enter a short description of the function of each parameter in a stored procedure.

In addition to the above reports, these two files are also necessary for SQLAutoDoc’s operation. Global.asa This configuration file executes automatically whenever anyone visits the SQLAutoDoc web site and sets session variables that tell the reports on which server the METADATA database is located. Default.asp This report is shown if the user does not specify a specific report, when visiting the SQLAutoDoc web site. Default.asp looks at the information contained in the session configuration variable and uses it to redirect the user to the Ddatabases.asp report.

How to install SQLAutoDoc

Here’s how you can set up SQLAutoDoc on your own server and start documenting your databases!

Setting up the database

To install SQLAutoDoc, you must first create a database on the SQL Server that you wish to document. The database should be named METADATA. The database shouldn’t grow that large, as it will contain only a few thousand records, divided among its ten tables.

Once the database has been created, run the MakeTables.SQL script in the METADATA database to create the SQLAutoDoc tables, and the MakeProcs.SQL script to create the stored procedures that insert or update records in those tables.

Next, go to the MASTER database on the same server, and run the MakeSystemProcs.SQL script. This will install the sp_DDScanForDatabases and sp_DDScanForTables stored procedures that are responsible for scanning all the databases on the server for new or updated tables, stored procedures or views and updating the tables in the METADATA as necessary.

Now that you’ve created the database and set up the tables and stored procedures that comprise SQLAutoDoc, it’s time to schedule the database scans. For most applications, it’s probably not necessary to execute the scan more than once a week.

You can use the SQL Server Enterprise Agent to schedule the stored procedure. If you’re in development or are making lots of changes to the schema of your database for other reasons, then you might want to schedule the scan more frequently. The scan doesn’t take very long. It should complete in less than a few minutes, even for servers with several databases containing very complicated schema.

Setting up the web server

To use the SQLAutoDoc reports, you’ll need a web server. Since Windows 2000 Server includes web server functionality by default, you could choose to place the reports on the SQL Server.

You’ll need to create a directory to contain the web reports. I recommend “Dictionary” as the name of the directory on the web server where the SQLAutoDoc reports will reside as it’s easy to remember. However, you can name the directory anything you like.

Into that directory you should copy the contents of the webreports zip file. Next, create a subdirectory called images and into it copy the contents of images.zip.

Once you’ve copied all of the files into their respective directories, you’ll need to modify the global.asa file to refer to your database server.

The global.asa file contains this statement:

Session("DataConn_ConnectionString") = "Driver={SQL Server};SERVER=myservername;DATABASE=METADATA;UID=myuserid;PWD=mypassword"

You should change myservername to the name of the SQL Server on which you have created the METADATA database. The myuserid and mypassword values should be replaced with a login id which has SELECT and UPDATE permissions for the tables in the METADATA database.

You will also need to use the Internet Information Services manager to make the directory that will contain the reports into a web application. This is so the global.asa file containing the location of the database server will be loaded automatically when you view your reports.

Conclusion

I hope that you find SQLAutoDoc useful in documenting your SQL Server databases. The system is just a start, and could be enhanced to automatically collect more information, such as information about indexes and constraints, and whether stored procedures are encrypted.

Download

Here are the scripts for creating the tables in the MetaData database, the stored procedures in the MetaData database and the stored procedures in the standard database.

First create a database on your server. Call it METADATA.
Run this script in the METADATA database to create the tables.
Run this script in the METADATA database to create the stored procedures that insert data into the tables.
Run this script in the MASTER database to create the stored procedures that will scan the server for new tables, views and stored procedures.
Now set up a scheduled job on your SQL Server to periodically execute the sp_DDScanForDatabases stored procedure
Finally copy the dictionary reporting ASP files to a directory on your web server and configure the GLOBAL.ASA file with your server's name, and the user id and password you wish the reports to use when connecting.