Extracting Metadata From SQL Server


select upper(b.name) as table_name, upper(a.name) as column_name,
a.colid, a.length, a.xprec, a.xscale, a.xtype,(CASE WHEN a.xtype=167 then 'varchar'
WHEN a.xtype=175 then 'char'
WHEN a.xtype=61 then 'datetime'
WHEN a.xtype=106 then 'decimal'
END) as col_type
from syscolumns a,
sysobjects b
where a.id=b.id
and b.xtype='U'
and upper(b.name) <> 'DTPROPERTIES'
order by b.name, a.name, a.colid


Tags: Other, SQL Server, SQL Tricks