Click here to Skip to main content
13,092,862 members (95,965 online)
Click here to Skip to main content
Add your own
alternative version

Tagged as


19 bookmarked
Posted 1 Dec 2012

Generate Data Dictionary from SQL Server

, 27 Feb 2013
Rate this:
Please Sign up or sign in to vote.
Easily find table description.


This tip shows how to find out table description and dependency with other tables.

Using the code

In any SQL Server database (any version of SQL Server) just create a Stored Procedure [spGenerateDBDictionary] that is given as attachment, and exec [spGenerateDBDictionary]. Then the procedure will return tables information with table name, data, attributes, data types, IsNullable info, primary key, foreign key constraints, any reference with another table, and the details description, but remember that the description will come from the description field where you give any description at the time of table creation.

USE [Database_Name]
-- =============================================
-- Author:JOHIR
-- Create date: 01/12/2012
-- =============================================
CREATE proc [dbo].[spGenerateDBDictionary] 

select [Table], [Attribute], [DataType],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



This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


About the Author

Johirul Islam Tarun
Software Developer Bitopi Group
Bangladesh Bangladesh
More than 5 Years of experience of Software Development area including both Desktop and web based application in the several domain including Banking, Garments Industries etc.

You may also be interested in...


Comments and Discussions

SuggestionDataedo - a great tool do generate data dictionary Pin
Piotr K11-Jun-15 12:20
memberPiotr K11-Jun-15 12:20 
QuestionTool for generate data dictionary in Excel Pin
cadocfrm25-Mar-15 13:40
membercadocfrm25-Mar-15 13:40 
Bugincorrect joins on data types, descriptions Pin
shriop17-Sep-14 15:13
membershriop17-Sep-14 15:13 
QuestionData Dictionary for Views Pin
ConlinAA23-Jan-14 6:59
memberConlinAA23-Jan-14 6:59 
GeneralThanks and my version for you all Pin
Abdiel26-Sep-13 9:36
memberAbdiel26-Sep-13 9:36 
GeneralRe: Thanks and my version for you all Pin
Johirul Islam Tarun23-Dec-13 23:44
memberJohirul Islam Tarun23-Dec-13 23:44 
SuggestionPrimary key Pin
Carlos de Freitas1-Aug-13 6:39
memberCarlos de Freitas1-Aug-13 6:39 
Generalfound one similar article with example Pin
iamonweb11-Jul-13 0:32
memberiamonweb11-Jul-13 0:32 
Generalone more article on data dictionary with example Pin
iamonweb11-Jul-13 0:31
memberiamonweb11-Jul-13 0:31 
Questionreally very helpful :) Pin
ahmed_am5523-Jun-13 16:43
memberahmed_am5523-Jun-13 16:43 
SuggestionGreat post and very helpful Pin
Cornell Emile9-May-13 8:24
memberCornell Emile9-May-13 8:24 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Permalink | Advertise | Privacy | Terms of Use | Mobile
Web04 | 2.8.170813.1 | Last Updated 27 Feb 2013
Article Copyright 2012 by Johirul Islam Tarun
Everything else Copyright © CodeProject, 1999-2017
Layout: fixed | fluid