Generate Data Dictionary from MS SQL Server

Ever being asked to produce documentation to your database? If you are like me, the latest version of such documentation may be 10 years old. A solution? Here is an exellent script to help you get started with data dictionary.

USE [Database_Name]
-- =============================================
-- Author:JOHIR
-- Create date: 01/12/2012
-- Modified by: Logic Flow: added column size
-- Modified on: 17/05/2013
-- =============================================
CREATE proc [dbo].[spGenerateDBDictionary]

select [Table], [Column], [DataType],
c.length [Size],
b.isnullable [Allow Nulls?],
CASE WHEN is null THEN 0
ELSE 1 END [PKey?],
CASE WHEN e.parent_object_id is null THEN 0
ELSE 1 END [FKey?],
CASE WHEN e.parent_object_id is null THEN '-'
ELSE END [Ref Table],
CASE WHEN h.value is null THEN '-'
ELSE h.value END [Description]
from sysobjects as a
join syscolumns as b
on =
join systypes as c
on b.xtype = c.xtype
left join (SELECT,sc.colid,
FROM syscolumns sc
JOIN sysobjects so ON =
JOIN sysindexkeys si ON =
and sc.colid = si.colid
WHERE si.indid = 1) d
       on = and b.colid = d.colid
left join sys.foreign_key_columns as e
on = e.parent_object_id
and b.colid = e.parent_column_id
left join sys.objects as g
on e.referenced_object_id = g.object_id
left join sys.extended_properties as h
on = h.major_id and b.colid = h.minor_id
where a.type = 'U' order by


Credits goes to Johirul Islam Tarun‘s contribution here

Spread the word. Share this post!

Leave A Reply

Your email address will not be published.