Database Documentation - Microsoft SQL Server 2005/2008

• Download source code - 17.96 KB
• Download deployment - 26.23 KB

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

• 2^nd March, 2009: Initial post