Posts Tagged ‘Creating Data dictionary’

As a DBA we have to maintain our all databases Dictionaries, Here is a Script , from which we can generate a full view of Tables structure details.

SELECT a.Name  [Table Name],
b.name  [Column Name],
c.name  [Data Type],
b.length [Column Length],
b.isnullable [Allow Nulls],
CASE WHEN d.name is null  THEN 0 ELSE 1 END  [Primary Key],
CASE WHEN e.parent_object_id is null THEN 0 ELSE 1 END [ForiegnKey],
CASE WHEN e.parent_object_id is null
THEN ‘-‘ ELSE g.name END [Reference Table],
       CASE WHEN h.value is null THEN ‘-‘ ELSE h.value END [Description]
from sysobjects as a
join syscolumns as on a.id b.id
join systypes as on b.xtype  c.xtype
left join (SELECT so.id,sc.colid,sc.name FROM syscolumns sc
JOIN sysobjects so ON o.id sc.id
JOIN sysindexkeys si ON so.id si.id and sc.colid  si.colid
WHERE si.indid = 1on a.id d.id and b.colid d.colid
Left join sys.foreign_key_columns as on a.id e.parent_object_id
          and b.colid e.parent_column_id
left join sys.objects as on e.referenced_object_id g.object_id
left join sys.extended_properties as on a.id h.major_id
and b.colid h.minor_id
where a.type ‘U’
order by a.name

We can also get somehow details, not as above , using below

SELECT  FROM INFORMATION_SCHEMA.COLUMNS

JJJ

Advertisements