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
-- Description: GENERATE DATA DICTIONARY FROM SQL SERVER
-- =============================================
CREATE proc [dbo].[spGenerateDBDictionary]
AS
BEGIN

select a.name [Table],
b.name [Column],
c.name [DataType],
c.length [Size],
b.isnullable [Allow Nulls?],
CASE WHEN d.name 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 g.name END [Ref Table],
CASE WHEN h.value is null THEN '-'
ELSE h.value END [Description]
from sysobjects as a
join syscolumns as b
on a.id = b.id
join systypes as c
on b.xtype = c.xtype
left join (SELECT so.id,sc.colid,sc.name
FROM syscolumns sc
JOIN sysobjects so ON so.id = sc.id
JOIN sysindexkeys si ON so.id = si.id
and sc.colid = si.colid
WHERE si.indid = 1) d
       on a.id = d.id and b.colid = d.colid
left join sys.foreign_key_columns as e
on a.id = 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 a.id = h.major_id and b.colid = h.minor_id
where a.type = 'U' order by a.name

END

Credits goes to Johirul Islam Tarun‘s contribution here

Leave A Reply

Your email address will not be published. Required fields are marked *

Time limit is exhausted. Please reload the CAPTCHA.