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