Click here to Skip to main content
11,573,666 members (56,774 online)
Click here to Skip to main content

SqlDoc: Document your SQL Server database

, 5 Sep 2004 203.3K 5.2K 107
Rate this:
Please Sign up or sign in to vote.
A small command prompt utility to help you document your SQL Server/MSDE database.

Sample Image - sqldoc.gif

Introduction

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

Background

In SQL Server, every property of tables, fields, relations, etc. are stored in a few system tables. You can query those tables to extract info 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, extracting table names, columns names, field types, lengths, and nullable columns, and generate a HTML file with that info. You can tweak the queries to extract any other info that you need.

There is a special piece of info 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 info 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 - connect to a specific server.
  • -U user_id - connect using a user name.
  • -P password - self explained.
  • -o outputFileName - self explained.

The source code, it's 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 info. This means that in future releases of SQL Server, the query could fail. If you don't find useful the 'description field', 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, " +
//    "COLUMN_NAME, COLUMN_DEFAULT, DATA_TYPE, " +
//    "CHARACTER_MAXIMUM_LENGTH, IS_NULLABLE " +
//    "FROM INFORMATION_SCHEMA.Columns " + 
//    "WHERE TABLE_NAME IN (" + SQL_GETTABLES + ")";

// this one gets columns info + the description field
private const string SQL_GETFIELDS = 
    "SELECT Sysobjects.name AS TABLE_NAME, " +
    "syscolumns.Id, syscolumns.name AS COLUMN_NAME, " + 
    "systypes.name AS DATA_TYPE, syscolumns.length" + 
    " as CHARACTER_MAXIMUM_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 syscolumns.id = sysobjects.id " + 
    "LEFT OUTER JOIN sysproperties ON " +
    "(sysproperties.smallid = syscolumns.colid" + 
    " AND sysproperties.id = syscolumns.id) " +
    "LEFT OUTER JOIN syscomments ON syscolumns.cdefault = syscomments.id " +
    "WHERE syscolumns.id IN " + 
    "(SELECT id FROM SYSOBJECTS WHERE xtype = 'U') " + 
    "AND (systypes.name <> '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.

Improvements

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

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here

Share

About the Author

Jose A. Gonzalvo
Web Developer
Spain Spain
Software developer

You may also be interested in...

Comments and Discussions

 
BugEl nombre de objeto 'sysproperties' no es válido. Pin
ingenvzla2-Jun-12 5:36
memberingenvzla2-Jun-12 5:36 
GeneralRe: El nombre de objeto 'sysproperties' no es válido. Pin
Anders Gustafsson23-May-14 2:52
memberAnders Gustafsson23-May-14 2:52 
QuestionProgram update for SQL2008, also added primary key identification. Pin
WillemSe22-Feb-12 2:14
memberWillemSe22-Feb-12 2:14 
I enhanced the program to make it work with SQL Server SQL2008 and also to add "(PK)" after fields that are in the primary key.

Open source code in Visual Studio 2010.
Open file SQLDoc.cs.

Replace declaration of the variable SQL_GETFIELDS with the code below, and recompile the program. I have not tested against SQL Server 2005 - only 2008.

Here is the new code:

private const string SQL_GETFIELDS = "SELECT so.name AS TABLE_NAME, syscolumns.Id, "+
"case "+
"when PK.ColID is not null then syscolumns.name + ' (PK)' "+
"else syscolumns.name "+
"end AS COLUMN_NAME, " +
"systypes.name AS DATA_TYPE, syscolumns.length as CHARACTER_MAXIMUM_LENGTH, exp.[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 so ON syscolumns.id = so.id  "+
"LEFT JOIN SysIndexKeys PK  on   "+
"( PK.id = syscolumns.id AND "+
" PK.colid = syscolumns.colid AND "+
" PK.IndId = (select indid from sysindexes where name =( "+
" select name from sysobjects where xtype = 'PK' and parent_obj = (select id from sysobjects where name = so.name)) "+
" ) "+
") "+
"LEFT OUTER JOIN sys.extended_properties exp ON  "+
"(exp.minor_id = syscolumns.colid AND exp.major_id = syscolumns.id) "+
"LEFT OUTER JOIN	syscomments ON syscolumns.cdefault = syscomments.id "+
"WHERE "+
"syscolumns.id IN (SELECT id FROM SYSOBJECTS WHERE xtype = 'U') AND (systypes.name <> 'sysname') "+
"ORDER BY syscolumns.colid ";

AnswerIs there any tool/utility which can generate the documentation from SQL Server 2005/2008 [modified] Pin
Amit Chaudhary17-Apr-09 4:48
memberAmit Chaudhary17-Apr-09 4:48 
GeneralStoredProcedure, For MS-SQL 2005 Pin
player.25-Oct-07 20:24
memberplayer.25-Oct-07 20:24 
Generalcouldn't make it work with sql server 2005 db! Pin
patrickdrd25-Sep-07 12:54
memberpatrickdrd25-Sep-07 12:54 
GeneralRe: couldn't make it work with sql server 2005 db! Pin
jag200625-Sep-07 23:36
memberjag200625-Sep-07 23:36 
AnswerRe: couldn't make it work with sql server 2005 db! Pin
Amit Chaudhary21-Apr-09 19:42
memberAmit Chaudhary21-Apr-09 19:42 
GeneralTry this SQL Pin
demogodyou9-Aug-07 20:11
memberdemogodyou9-Aug-07 20:11 
AnswerRe: Try this SQL Pin
Amit Chaudhary21-Apr-09 19:42
memberAmit Chaudhary21-Apr-09 19:42 
GeneralGet Table Descriptions Pin
nameoraliasnameoraliastv19-Dec-06 10:29
membernameoraliasnameoraliastv19-Dec-06 10:29 
GeneralSQL Server 2005 Pin
pblse39-Jun-06 0:29
memberpblse39-Jun-06 0:29 
GeneralRe: SQL Server 2005 Pin
pjwallace28-Jul-09 9:02
memberpjwallace28-Jul-09 9:02 
Questionhow to Pin
canozurdo5-May-06 7:25
membercanozurdo5-May-06 7:25 
GeneralDocument Foreign Keys Pin
canozurdo5-May-06 7:23
membercanozurdo5-May-06 7:23 
GeneralBUG with tables with same name Pin
canozurdo5-May-06 7:21
membercanozurdo5-May-06 7:21 
GeneralModification Pin
sides_dale8-Nov-05 14:48
membersides_dale8-Nov-05 14:48 
Generalsort before it generates Pin
Unruled Boy13-Aug-05 17:37
memberUnruled Boy13-Aug-05 17:37 
GeneralInvalid Object Pin
computerguru9238230-Jul-05 6:36
membercomputerguru9238230-Jul-05 6:36 
GeneralRe: Invalid Object Pin
gyyggyyg23-Aug-05 3:04
membergyyggyyg23-Aug-05 3:04 
GeneralRe: Invalid Object Pin
Jose A. Gonzalvo23-Aug-05 5:28
memberJose A. Gonzalvo23-Aug-05 5:28 
GeneralRe: Invalid Object Pin
computerguru9238225-Aug-05 12:19
membercomputerguru9238225-Aug-05 12:19 
GeneralRe: Invalid Object Pin
Jose A. Gonzalvo25-Aug-05 21:13
memberJose A. Gonzalvo25-Aug-05 21:13 
GeneralRe: Invalid Object Pin
eyal skiba11-Jan-10 1:37
membereyal skiba11-Jan-10 1:37 
GeneralRe: Invalid Object Pin
Paul Conrad11-Jan-10 4:19
mvpPaul Conrad11-Jan-10 4:19 

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

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

| Advertise | Privacy | Terms of Use | Mobile
Web04 | 2.8.150624.2 | Last Updated 6 Sep 2004
Article Copyright 2004 by Jose A. Gonzalvo
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid