Click here to Skip to main content
13,297,266 members (71,126 online)
Click here to Skip to main content
Add your own
alternative version


2 bookmarked
Posted 10 Jun 2011

Database Table Documentation with a Data Dictionary

, 10 Jun 2011
Rate this:
Please Sign up or sign in to vote.
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

-- Create the Column Listing table:
    [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 

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 
    -- 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,, colorder, sysc.[name] 
FROM syscolumns sysc
INNER JOIN sysobjects syso ON =
INNER JOIN TableInfo ti ON ti.tablename = syso.[name]

-- Select the records:
    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.


This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


About the Author

Andrew Zwicker
United States United States
No Biography provided

You may also be interested in...


Comments and Discussions

QuestionTry a free tool Pin
Dataedo2-Aug-15 10:20
memberDataedo2-Aug-15 10:20 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Permalink | Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.171207.1 | Last Updated 10 Jun 2011
Article Copyright 2011 by Andrew Zwicker
Everything else Copyright © CodeProject, 1999-2017
Layout: fixed | fluid