Over the years I’ve done quite a bit of scripting. Many of those scripts SELECT, INSERT or UPDATE a SQL database. The other day I was looking at my tables and realized I didn’t know what (or if) some of them were used for and I didn’t have very good documentation either. I decided to start making some documentation and thought the best place to keep the information would be as a new table in the database itself — that way I wouldn’t need to look too hard for it. I created a new table which contains a list of other tables, a description of what the data is for, a contact email address and a column for a list of systems/scripts that either use or update the data. Useful information, but rather basic and doesn’t really warrant a blog post…
Here is the creative part. I needed a system to track and notify when a table was added to the database but not added to my documentation. First we start with a rather simple select statement that I found with a bit of googling. This select statement returns a list of tables, when they were created and the number of columns in the table:
SELECT name, create_date, max_column_id_used FROM sys.Tables
Once we have this information, we use my favorite WHERE clause to make it better — NOT IN! The following select statement is just like the one above, only in the where clause we filter out only the tables that are not in the new documentation table:
SELECT name, create_date, max_column_id_used FROM sys.Tables WHERE name NOT IN (SELECT distinct(tableName) from tblDatabaseDocumentation) ORDER BY name
I then created a scheduled task to run daily that executes the above SQL statement. If there is one or more results I get an email notification. This reminds me to update my documentation or find out who created the table and remind them to document their work.