Saturday, July 18, 2015

complete information from database

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

No comments: