Click here to Skip to main content
14,827,196 members
Articles / Database Development / SQL Server
Posted 5 Sep 2004


108 bookmarked

SqlDoc: Document your SQL Server Database

Rate me:
Please Sign up or sign in to vote.
4.73/5 (36 votes)
5 Sep 2004CPOL2 min read
A small command prompt utility to help you document your SQL Server/MSDE database

Sample Image - sqldoc.gif


This article presents a small command prompt utility to help you document your SQL Server/MSDE database.


In SQL Server, every property of tables, fields, relations, etc. are stored in a few system tables. You can query those tables to extract information about your database. SQL Server provides a mechanism to simplify the queries to those system tables: Information Schema.

Try to run the command "SELECT * FROM INFORMATION_SCHEMA.columns" in any database. A list of every column in every table in the database will be returned.

I code this utility to auto document my databases, extract table names, columns names, field types, lengths, and nullable columns, and generate a HTML file with that information. You can tweak the queries to extract any other information that you need.

There is a special piece of information that is not available through Information Schema: column's description field. I find it very useful to fill that field when I design a table, to clarify the purpose of a column. To extract that description, you must bypass Information Schema and query directly the system tables.

How It Works

The inner working of this app is very simple. It gets the results of special queries, and writes an XML file which is used in combination with an XSLT file to produce the final HTML file.

Using the Code

You can quick test the utility, extracting information about the Northwind database, typing:

SqlDoc.exe -E -d northwind

This will produce the file output.html.

I've tried to mimic the OSQL command switches. The available switches are:

  • -E - uses integrated security to connect SQL Server
  • -S server_name - connects to a specific server
  • -U user_id - connects using a user name
  • -P password - self explained
  • -o outputFileName - self explained

The source code is very simple to follow and it's self-describing.

Note that as commented before, in order to extract the column's description field, the code bypasses the standard way to extract columns information. This means that in future releases of SQL Server, the query could fail. If you don't find the 'description field' useful, you can comment/uncomment these lines to use the method you want.

// most compatible way to retrieve column info but lacks 
// description field
//private const string SQL_GETFIELDS = "SELECT TABLE_NAME, " +

// this one gets columns info + the description field
private const string SQL_GETFIELDS = 
    "syscolumns.Id, AS COLUMN_NAME, " + 
    " AS DATA_TYPE, syscolumns.length" + 
    "sysproperties.[value] AS COLUMN_DESCRIPTION,  " +
    "syscomments.text as COLUMN_DEFAULT, " +
    "syscolumns.isnullable as IS_NULLABLE " +
    "FROM syscolumns INNER JOIN systypes " +
    "ON syscolumns.xtype = systypes.xtype " +
    "LEFT JOIN sysobjects ON = " + 
    "LEFT OUTER JOIN sysproperties ON " +
    "(sysproperties.smallid = syscolumns.colid" + 
    " AND = " +
    "LEFT OUTER JOIN syscomments ON syscolumns.cdefault = " +
    "WHERE IN " + 
    "(SELECT id FROM SYSOBJECTS WHERE xtype = 'U') " + 
    "AND ( <> 'sysname')" +
    "ORDER BY syscolumns.colid";

Points of Interest

Leaving apart the purpose of this app, I was interested in learning about the XSLT transformations. Included in the demo project is a test XSLT file. You can edit this file in order to improve the output format.


SQL Server stores tons of information about each database, relations, key columns, views, stored procedures, etc. So fully documenting the database, it's the way to go.


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


About the Author

Jose A. Gonzalvo
Web Developer
Spain Spain
Software developer

Comments and Discussions

GeneralWould like to try it, but gets error Pin
jb30010-Mar-05 0:41
Memberjb30010-Mar-05 0:41 
GeneralRe: Would like to try it, but gets error Pin
Jose A. Gonzalvo5-May-05 10:29
MemberJose A. Gonzalvo5-May-05 10:29 
GeneralMetadata Question Pin
Delroy26-Jan-05 5:57
MemberDelroy26-Jan-05 5:57 
GeneralRe: Metadata Question Pin
Jose A. Gonzalvo26-Jan-05 6:08
MemberJose A. Gonzalvo26-Jan-05 6:08 
GeneralRe: Metadata Question Pin
Delroy26-Jan-05 6:14
MemberDelroy26-Jan-05 6:14 
GeneralBug with User Defined Data Types Pin
mrphonig13-Sep-04 21:20
Membermrphonig13-Sep-04 21:20 
GeneralRe: Bug with User Defined Data Types Pin
Jose A. Gonzalvo14-Sep-04 2:42
MemberJose A. Gonzalvo14-Sep-04 2:42 
GeneralCollation / Case Sensitivity Pin
Josh Blair8-Sep-04 10:50
MemberJosh Blair8-Sep-04 10:50 
GeneralRe: Collation / Case Sensitivity Pin
Jose A. Gonzalvo26-Aug-05 7:30
MemberJose A. Gonzalvo26-Aug-05 7:30 
GeneralReal Good Work Pin
Saleem Beeravu8-Sep-04 7:50
MemberSaleem Beeravu8-Sep-04 7:50 
GeneralTry MyGeneration Pin
Anonymous8-Sep-04 7:41
MemberAnonymous8-Sep-04 7:41 
GeneralDbDocumenter CodeSmith Templates Pin
Eric J. Smith7-Sep-04 16:34
MemberEric J. Smith7-Sep-04 16:34 
GeneralRe: DbDocumenter CodeSmith Templates Pin
Jose A. Gonzalvo7-Sep-04 20:05
MemberJose A. Gonzalvo7-Sep-04 20:05 
GeneralRe: DbDocumenter CodeSmith Templates Pin
Oskar Austegard9-Sep-04 6:34
MemberOskar Austegard9-Sep-04 6:34 
GeneralTable description Pin
zoomba7-Sep-04 7:13
Memberzoomba7-Sep-04 7:13 
GeneralRe: Table description Pin
Jose A. Gonzalvo7-Sep-04 8:00
MemberJose A. Gonzalvo7-Sep-04 8:00 
Generalcolumn description Pin
adamdev6-Sep-04 22:07
Memberadamdev6-Sep-04 22:07 
GeneralRe: column description Pin
Jose A. Gonzalvo6-Sep-04 22:44
MemberJose A. Gonzalvo6-Sep-04 22:44 
GeneralRe: column description Pin
adamdev6-Sep-04 23:16
Memberadamdev6-Sep-04 23:16 
GeneralRe: column description Pin
Jose A. Gonzalvo7-Sep-04 0:38
MemberJose A. Gonzalvo7-Sep-04 0:38 
GeneralNice Pin
Charlie Williams6-Sep-04 11:34
MemberCharlie Williams6-Sep-04 11:34 
GeneralRe: Nice Pin
Jose A. Gonzalvo6-Sep-04 20:06
MemberJose A. Gonzalvo6-Sep-04 20:06 

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.