Click here to Skip to main content
Click here to Skip to main content

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
ArticleFinalScreen.JPG

Finaloutput__Medium_2.JPG

Introduction

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.

Background

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).

Code

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:

SELECT NAME,TYPE,TYPE_DESC,OBJECT_ID,schema_id FROM sys.tables

--    Or 

SELECT NAME,TYPE,TYPE_DESC,OBJECT_ID,schema_id FROM sys.objects WHERE Type = 'U'

To get all the schema associated with a table:

SELECT schemas.name 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:

SELECT  A.NAME,B.Name as DataType,A.MAX_LENGTH,
     A.PRECISION ,A.OBJECT_ID,A.SYSTEM_TYPE_ID,a.IS_Nullable,c.definition FROM      
    SYS.COLUMNS A INNER JOIN SYS.TYPES  B ON B.user_type_id=A.SYSTEM_TYPE_ID 
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,d.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 a.name as IndexName,a.type_desc + 
        Case a.is_unique
        When 1
            Then ',Unique '
        else 
              ',Not Unique '
             End
        +
           Case a.is_primary_key
        When 1
           Then ',Primary Key '
        else
               ',Not Primary Key '
               End
         as IndexDescription  ,
        C.name as ColumnName 
        FROM sYS.INDEXES a
        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> )       

Algorithm

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.

History

  • 2nd March, 2009: Initial post

License

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

Share

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.

Comments and Discussions

 
GeneralMy vote of 1 PinmemberKinStephen2-Mar-09 7:58 
GeneralRe: My vote of 1 PinmemberMarc Leger2-Mar-09 8:20 
AnswerRe: My vote of 1 PinmemberMember 45398502-Mar-09 10:37 

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 | Mobile
Web02 | 2.8.140821.2 | Last Updated 2 Mar 2009
Article Copyright 2009 by pavan k vadlamudi
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid