Click here to Skip to main content
13,344,508 members (48,545 online)
Click here to Skip to main content
Add your own
alternative version


52 bookmarked
Posted 2 Mar 2009

Database Documentation - Microsoft SQL Server 2005/2008

, 2 Mar 2009
Rate this:
Please Sign up or sign in to vote.
Creates database documentation table(s) schema



It is known best practice in development houses to have database schema as part of development life cycle. Database schema is also necessary for multiple-development teams working on a single goal. It is also that .NET developers may not know SQL system object information fully.

Here is a small tool I have developed which generates database documentation in XML for Microsoft SQL server 2005 onwards. The tool was written in VB.NET using Microsoft .NET Framework 2.0. I am sure many of you have already tried it somewhere in the world.

The tool generates:

  • All the tables names along with schema.
  • All the columns associated with the table.
  • All the column properties such data type, length, accepts Null and default value.
  • Constraint information such as primary key and characteristics of an identity column.


All the user databases, table(s), columns, constraints, indexing, file information and characteristics are stored in the database engine. Microsoft SQL server providers catalogue views interface to retrieve any database object information. Note catalogue views do not provide information about replication, backup or SQL server agent catalogue data.

I used catalogue views to obtain information of the database objects (tables, columns, etc.). You can find more information on MSDN (Object Catalog Views) or SQL server 2005 books online (ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/e8670a6b-e15c-4126-8d2d-c9cfb968a12d.htm).


This solution was built in Visual Studio 2008. I have referenced the following .NET Framework 2.0 namespaces System, System.configuration, System.Data, System.Deployment, System.Drawing, System.Windows.Forms, System.Xml, and used Microsoft Application Block for Data Access in .NET for Data Access Operations. More information can be found here. The project consists of a single Windows Form and App.config files. On Windows form, I have a button and textbox. Button is used to start the processing. Textbox is used to track the progress.

You can execute an executable file in bin folder by entering SQL Server, database name in their respective textboxes and click Create button on the form.

Essential Queries

Here are queries required to obtain properties of tables such as name, schema, columns, identity columns, primary key columns and index columns. You can also use them in your standard query tool (SQL server management query or query analyser) code to obtain information of database tables.

To get all the table names, I have to run the following query against the database:


--    Or 


To get all the schema associated with a table:

SELECT FROM sys.schemas schemas 
WHERE schemas.schema_id =<TableObjectID>

To get all the columns in a table, I have used the following query against the database:

     A.PRECISION ,A.OBJECT_ID,A.SYSTEM_TYPE_ID,a.IS_Nullable,c.definition FROM      
left outer  JOIN sys.default_constraints C ON c.OBJECT_ID = a.Default_object_id 
WHERE A.object_id=    <TableObjectID>    

To get all the information related with Identity of column:

SELECT NAME,seed_value,increment_value, is_not_for_replication 
FROM sys.identity_columns where object_id=<TableObjectID>    

To get primary key information of given table:

SELECT a.NAME, as ColumnName 
   FROM sys.key_constraints a
   INNER JOIN sys.indexes  b
   on a.PARENT_object_id= b.object_id
   INNER JOIN sys.index_columns C
   on C.object_id=a.PARENT_object_id AND B.index_id = c.index_id
   INNER JOIN sys.columns d
    ON D.object_id = A.PARENT_object_id
    AND C.column_id = d.Column_id
    WHERE(a.PARENT_object_id =  & <TableID>
    AND b.is_primary_key=1 AND a.type='PK'    

To get primary key information of given table:

SELECT as IndexName,a.type_desc +
    Case a.is_unique
    When 1
        Then ',Unique '
          ',Not Unique '
       Case a.is_primary_key
    When 1
       Then ',Primary Key '
           ',Not Primary Key '
     as IndexDescription  , as ColumnName
    INNER JOIN sys.index_columns b
    on b.object_id=a.object_id  AND A.index_id = b.index_id
    INNER JOIN sys.columns c
    ON c.object_id = A.object_id
    AND b.Column_id = c.column_id
    WHERE(a.object_id = <ObjectID> )


Step 1: Create XML document object.

Step 2: Create Root node.

Step 3: Get all Table names.

Step 4: Iterate through every table.

Step 5: Create a node with Table name.

Step 6: Retrieve all the fields characteristics (Column name, datatype, length, Nullable and default for each table.

Step 7: Create XML element for each column name and attach it to Column node.

Step 8: Retrieve primary key (Name and Column name) information for each table.

Step 9: Create primary key XML node.

Step 10: Create Name and Column XML elements and attach them to Primary key XML node.

Step 11: Retrieve all the indexes (Index name, column name and description) information for each table.

Step 12: Create Index XML node.

Step 13: Create XML element for Index name, description, column name and attach them to Index XML node.

Step 14: Attach index XML node, Primary XML node, Column node to the Table node.

Step 15: For each step simple log to text box.

Step 16: Save XML document object as XML file.

Step 17: Define your XSL sheet to apply style to the raw XML file.

Step 18: Run the Iexplore to show the final output in UI (see second screen shot).

XSL Styling

You can execute the executable file in bin folder by entering SQL Server, database name in their respective textboxes and click Create button on the form.

You can modify test.xsl in bin folder to customize colors, fonts, etc.

Points of Interest

There is scope to improve XSL design. You can also populate SQL server names in combo box and database names automatically using SQL server SMO namespace library.


  • 2nd March, 2009: Initial post


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


About the Author

pavan k vadlamudi
Software Developer (Senior)
Ireland Ireland
Started working IT since year 1997, with humble C, C++ and small Access Applications. Now working in Ireland for the past 7 years in various Microsoft technologies.

You may also be interested in...

Comments and Discussions

GeneralTks for sharing! Pin
Jorge Cirilo13-Feb-11 11:23
memberJorge Cirilo13-Feb-11 11:23 
GeneralMy vote of 4 Pin
daw_id12-Aug-10 13:11
memberdaw_id12-Aug-10 13:11 
GeneralFine article but missing extended properties Pin
Calle Manthey11-Mar-09 4:43
memberCalle Manthey11-Mar-09 4:43 
GeneralMy vote of 1 Pin
KinStephen2-Mar-09 8:58
memberKinStephen2-Mar-09 8:58 
GeneralRe: My vote of 1 Pin
Marc Leger2-Mar-09 9:20
memberMarc Leger2-Mar-09 9:20 
AnswerRe: My vote of 1 Pin
Member 45398502-Mar-09 11:37
memberMember 45398502-Mar-09 11:37 

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.180111.1 | Last Updated 2 Mar 2009
Article Copyright 2009 by pavan k vadlamudi
Everything else Copyright © CodeProject, 1999-2018
Layout: fixed | fluid