Thursday, March 24, 2011

Generate SQL Server Data dictionary

SQL server comes with many built-in Stored Procedures and Views; we know them as system view or stored procedure.

While design of project documentation we often need to generate Dictionary which lists all the tables along with Columns, Data type and Description of columns.

There isn’t any tool or command which Extract this information directly. We can write SQL query using system stored procedures and system views.



1) Using System Stored Procedure.

We have stored procedure called sp_columns. This procedure returns column information for the specified tables or views.

Procedure sp_columns has five parameters. Parameters we are going to use are explained here.



Table_name
This parameter accept name of table without schema, and returns column information.
NULL value of this parameter returns Columns for All tables in the database.
table_owner
Takes owner as argument, if specified will returns columns of tables for that schema only.



Here is the query using sp_columns stored procedure.



declare @ColumnTable table(

TABLE_QUALIFIER varchar(100),

TABLE_OWNER varchar(100),

TABLE_NAME varchar(100),

COLUMN_NAME varchar(100),

DATA_TYPE varchar(100),

[TYPE_NAME] varchar(100),

"PRECISION" varchar(100),

"LENGTH" varchar(100),

SCALE varchar(100),

RADIX varchar(100),

NULLABLE varchar(100),

REMARKS varchar(100),

COLUMN_DEF varchar(100),

SQL_DATA_TYPE varchar(100),

SQL_DATETIME_SUB varchar(100),

CHAR_OCTET_LENGTH varchar(100),

ORDINAL_POSITION varchar(100),

IS_NULLABLE varchar(100),

SS_DATA_TYPE varchar(100)

)

insert into @ColumnTable exec sp_columns null,@table_owner='dbo'



select * from @ColumnTable



2) Using System views.



Some system views returns information about table schema defined in the current database.

sys.columns
Returns a row for each column of an object that has columns.
sys.tables
Returns a row for each table object.
sys.systypes
Returns one row for each system-supplied data type.



We can write following query to returns table column information using system views.



select tab.name, cols.name,typs.name,cols.system_type_id from sys.columns cols

inner join sys.tables tab on tab.object_id =cols.object_id

LEFT OUTER JOIN sys.systypes typs on typs.xtype=cols.system_type_id





You can use any of the option to generate Data Dictionary and then Copy result directly in the EXCEL.

You can refer MSDN for more information about views and stored procedures.

As far as my knowledge there is not any option in Management Studio/Visual Studio to generated Data Dictionary.

If you know any better options please let us know.



Hope this will help.