Why table and column documentation is useful and some SQL for setting up the data dictionary tables.

At Boston Public Schools, we have many hundreds of data tables. Some are used all the time, others are used only during certain times of the year, while others are simply backups or are no longer used. We recently went through the process of identifying and documenting the important tables in the system. The result of this was a data dictionary in the form of two additional tables – one with table level documentation and one with column-level documentation.

Why create a data dictionary? For Boston Public Schools, the main reason is we’re going to be working with an outside vendor to roll out a new student information system over the upcoming year. This data documentation will serve as a good starting point for them to learn about our existing data. An additional benefit is it helps new hires learn about the many different tables in our system. Also, it serves as a comprehensive, searchable reference for any application developer. If I don’t know what a particular column is used for, the data dictionary is now the first place to check.

Here’s the SQL for creating the two tables:

-- Create the Table Listing table:
CREATE TABLE [dbo].[TableInfo](
[tableid] [INT] PRIMARY KEY IDENTITY(1,1) NOT NULL ,
[servername] [VARCHAR](20) NOT NULL,
[databasename] [VARCHAR](20) NOT NULL,
[tablename] [VARCHAR](20) NOT NULL,
[tableGroup] [VARCHAR](30) NULL,
[description] [VARCHAR](1000) NULL
) ON [PRIMARY]
 
-- Create the Column Listing table:
CREATE TABLE [DataInfo] (
[columnid] [INT] IDENTITY (1, 1) NOT NULL ,
[tableid] [INT] NOT NULL FOREIGN KEY REFERENCES [TableInfo](tableid) ,
[columnname] [VARCHAR] (50) NOT NULL ,
[columnorder] [INT] NOT NULL ,
[description] [VARCHAR] (400) NOT NULL ,
[tableref] [VARCHAR] (120) NULL , -- used to document foreign keys.
[lutyperef] [VARCHAR] (40)  NULL , -- used to specify this value is a code from the lookup table.
[comments] [VARCHAR] (2000) NULL 
) ON [PRIMARY]

With the two tables in place, we now need to insert the baseline records. Here’s the SQL to accomplish this [for demonstration purposes, I'm using the Adventureworks database - I'm doing this on SQL Server 2005]:

-- Insert the table listing:
INSERT INTO TableInfo (servername, databasename, tablename, tableGroup, [description] )
SELECT DISTINCT 'MyServer', 'AdventureWorks', tablename, 'Uncategorized', tablename 
FROM 
(
-- This list would normally have many tables:
SELECT'Address' AS 'tablename' -- need to have an alias for the column name.
UNION SELECT 'Contact'
UNION SELECT 'StateProvince'
) tablesToInclude 
INNER JOIN sysobjects syso ON syso.[name] = tablesToInclude.tablename AND xtype = 'U'
WHERE tablename NOT IN(SELECT tablename FROM TableInfo)
 
-- Insert the column listing:
INSERT INTO DataInfo(tableid, columnname, columnorder, description)
SELECT ti.tableid, sysc.name, colorder, sysc.[name] 
FROM syscolumns sysc
INNER JOIN sysobjects syso ON sysc.id = syso.id
INNER JOIN TableInfo ti ON ti.tablename = syso.[name]
 
-- Select the records:
SELECT 
ti.servername, 
ti.databasename, 
tableGroup, 
ti.description AS 'tableDescription', 
columnname, 
di.description AS 'columndescription', 
di.comments AS 'columncomments'
FROM TableInfo ti
INNER JOIN DataInfo di 
ON ti.tableid = di.tableid

In the TableInfo insert statement, the join on sysobjects is done to ensure the tables actually exists in the system. At BPS, we had over 100 tables in the derived table listing, and I used textpad to help quickly come up with the SQL for the derived table. [In a future post, I'll describe how textpad and even excel can help make query writing easier].

In the DataInfo insert statement, I included colorder as a field so that, when we query these tables, the records can be returned in the expected order. When I do ‘select * …’ from a table, SQL Server returns the records in a predictable ordering. I wanted to preserve that same ordering when returning results from DataInfo.

The SQL statements above create the records, but the tables and columns are still not documented at this point. In my next post I’ll discuss the SQL involved with doing the actual documentation.