Create Data Dictionary in SQL Database

This query will create a Data dictionary of a database which can be very helpful while doing the documentation of a project.

select distinct

s.name as 'Schema_Name',

o.[name] as 'Table_Name',

c.[name] as 'Column_Name',

t.[name] as 'Column_Datatype',

case when (c.ISNULLABLE = 0) then 'NO' else 'YES' END AS 'Is_Nullable',

case when (t.[name] = 'numeric') then '' else c.[length] end as 'Column_Length',

case when (t.[name] != 'numeric') then '' else c.prec end as 'Column_Precision',

--case when (t.[name] != 'numeric') then '' else c.scale end as 'Column_Scale',

case when (pks.pkscolid is not null) then 'YES' else '' end as 'Is_PK',

case when (vfk.ref_table_name is not null) then 'YES' else '' end as 'Is_FK',

ISNULL(vfk.REF_TABLE_NAME, '') as 'Referenced_Table',

ISNULL(vfk.REF_COLUMN_NAME, '') as 'Referenced_Column',

case when (Select count(1) from sysindexkeys WITH (NOLOCK) where id = c.id and colid = c.colid)>0 then 'YES' else '' END AS 'Column_Has_Any_Index',

'' as 'Column_Description'

from sysobjects o WITH (NOLOCK)

inner join sys.tables ta WITH (NOLOCK)

on o.id = ta.object_id

inner join sys.schemas s WITH (NOLOCK)

on ta.schema_id = s.schema_id

inner join syscolumns C WITH (NOLOCK) on o.id = c.id

inner join systypes t WITH (NOLOCK) on c.xtype = t.xtype

left outer join (

SELECT

--Name of the FOREIGN KEY constraint

SO3.NAME FK_NAME,

--Unique ID of FOREIGN KEY constraint

SO3.ID FK_ID,

-- Owner of the table with the FOREIGN KEY constraint

SU.NAME TABLE_OWNER,

--Name of the table with the FOREIGN KEY constraint

SO.NAME TABLE_NAME,

--Object identification number.

SO.ID TABLE_ID,

--Name of the Column with the FOREIGN KEY constraint

SC.NAME COLUMN_NAME,

--Position of the column in the FOREIGN KEY constraint

SC.COLID COLID,

--Owner of the table referenced in the FOREIGN KEY constraint

SU2.NAME REF_TABLE_OWNER,

--Name of the table referenced in the FOREIGN KEY constraint

SO2.NAME REF_TABLE_NAME,

--Object identification number of the table referenced in the FOREIGN KEY constraint.

SO2.ID REF_TABLE_ID,

--Name of the column referenced in the FOREIGN KEY constraint

SC2.NAME REF_COLUMN_NAME,

--Position of the column in the reference column list

SC2.COLID REF_TABLE_COLID

FROM SYSFOREIGNKEYS SYSFK WITH (NOLOCK)

--Foreign Key Constraint - Table info

INNER JOIN (SELECT UID, ID, NAME FROM SYSOBJECTS WITH (NOLOCK) WHERE XTYPE = 'U') SO

ON SYSFK.FKEYID = SO.ID

--Referenced in the FOREIGN KEY constraint - Table info

INNER JOIN (SELECT UID, ID, NAME FROM SYSOBJECTS WITH (NOLOCK) WHERE XTYPE = 'U') SO2

ON SYSFK.RKEYID = SO2.ID

--Foreign Key Constraint - Column info

INNER JOIN (select ID, COLID, NAME FROM SYSCOLUMNS WITH (NOLOCK)) SC ON SYSFK.FKEYID =

SC.ID AND SYSFK.FKEY = SC.COLID

--Referenced in the FOREIGN KEY constraint - Column info

INNER JOIN (select ID, COLID, NAME FROM SYSCOLUMNS WITH (NOLOCK)) SC2 ON SYSFK.RKEYID

= SC2.ID AND SYSFK.RKEY = SC2.COLID

--Name and ID of the FOREIGN KEY constraint

INNER JOIN (SELECT ID, NAME FROM SYSOBJECTS WITH (NOLOCK)) SO3 ON SYSFK.CONSTID =

SO3.ID

--Foreign Key Constraint - Owner info

INNER JOIN SYSUSERS SU ON SO.UID = SU.UID

--Referenced in the FOREIGN KEY constraint - Owner info

INNER JOIN SYSUSERS SU2 ON SO2.UID = SU2.UID

) vfk

on c.id = vfk.table_id and c.colid = vfk.colid

left outer join

(select o.id pksid, c.colid pkscolid

from sysindexes i WITH (NOLOCK)

join sysobjects o WITH (NOLOCK) ON i.id = o.id

join sysobjects pk WITH (NOLOCK) ON i.name = pk.name

AND pk.parent_obj = i.id

AND pk.xtype = 'PK'

join sysindexkeys ik WITH (NOLOCK) on i.id = ik.id

and i.indid = ik.indid

join syscolumns c WITH (NOLOCK) ON ik.id = c.id

AND ik.colid = c.colid) pks

on c.id = pks.pksid AND c.colid = pks.pkscolid

where o.type = 'u'

and o.name not like 'sysdiagrams'

and t.[name] <> 'sysname'

order by o.[name]