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 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 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 , [lutyperef] [VARCHAR] (40) NULL , [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 INTO TableInfo (servername, databasename, tablename, tableGroup, [description] )
SELECT DISTINCT 'MyServer', 'AdventureWorks', tablename, 'Uncategorized', tablename
SELECT 'Address' AS 'tablename' UNION SELECT 'Contact'
UNION SELECT 'StateProvince'
INNER JOIN sysobjects syso ON syso.[name] = tablesToInclude.tablename AND xtype = 'U'
WHERE tablename NOT IN(SELECT tablename FROM TableInfo)
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]
ti.description AS 'tableDescription',
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.