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 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 o.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
We can also get somehow details, not as above , using below
SELECT * FROM INFORMATION_SCHEMA.COLUMNS
JJJ
