JLION.COM
SQLAutoDoc - SQL Server database dictionary & monitoring utility
Created 5/20/03 Last Updated 12/14/07 SQL Server, Utilities

SQLAutoDoc
- SQL Server database dictionary & monitoring utility v2.0.0.0

SQLAutoDoc Features:

  • Maintains a history of schema changes to a database.
  • Shows the database schema at a point in time.
  • Ability to attach descriptions to stored procedures, views and tables, as well as to table fields.
  • Search for for text in names of fields or in the text of views and stored procedures.
  • Compare different versions of a database to see which tables, views and stored procedures have changed.
  • Tracking and charting of database and table size over time.
  • Tracking and charting of database wait states.
  • Tracking and charting of database blocking.
  • Tracking and charting of database connections.
  • Sends out periodic email reports.

SQLAutoDoc can be used and distributed without restriction. I would be interested in hearing from you if you have suggestions for improvements. You can download it here:

Download SQLAutoDoc!


How to use SQLAutoDoc

What SQLAutoDoc Does:

SQLAutoDoc has been my pet project for a few years now and has evolved through several stages. It started off simply as a couple of stored procedures and a set of simple web pages that enabled me to list the tables, stored procedures and views in the databases on a SQL Server for the purpose of documenting them for other developers.

I wrote SQLAutoDoc because I support large numbers of small SQL Server-based applications. I need to track changes made to their database's schema as some business logic is contained within views and stored procedures and I occasionally need to revert to earlier versions of those views and stored procedures. I am also frequently working remotely so saving copies of the views and stored procedures as text then placing them in a version control system such as SourceSafe would be time-consuming and potentially unreliable.

It is also helpful, when diagnosing performance issues or planning for capacity requirements, to know the rate of growth in size of the database.

As time permitted, I've moved the database scanning functionality into a windows service and have greatly enhanced the functionality provided by the web pages. In its current incarnation, SQLAutoDoc does the following, and even more:

The first version of SQLAutoDoc was entirely SQL Server based and depended on creating several stored procedures in the master database of a server as well as a scheduled DTS job to execute them. I found that many DBAs do not like to modify the master database of a server in any way (surprise, surprise) so that it sometimes was difficult to deploy the software.

In planning this second version I decided to make SQLAutoDoc a web application rather than entirely a SQL Server application. Doing so allows for:

  • Monitoring of multiple servers.
  • Support for SQL Server 2005 Express
  • Support for servers where it is not possible to modify the master database, or where SQLAutoDoc must run at a reduced level of permissions.

All this is accomplished by periodically polling each database. This means that what you see in the SQLAutoDoc reports is a sampling. If you've got the schema polling function set to scan once per day and you make multiple changes to a single stored procedure during the day, only the change that's present when the database is polled will be stored in the SQLAutoDoc database. The other scans work like this, too.

When viewing the data it's important to keep this in mind. When SQLAutoDoc reports a dramatic increase in blocking for a day what it's really telling you is that each time it polled the database, it found many blocks. It doesn't tell you about what happened between the times that it polled when there could have been many blocks, or none at all. This same caveat is also true for connection, size, and waitstate reporting.

There is also no inherent security mechanism built into SQLAutoDoc at this time. To restrict access to the SQLAutoDoc reports, you'll need to use Windows Directory Permissions.