select table_name=case when b.colorder=1 then a.name else '' end,
column_name=b.name,
column_order=b.colorder,
data_type=t1.name
+case when t1.name in ('binary','varbinary','char','varchar')
then '('+cast(b.length as varchar(10))+')'
when t1.name in ('nchar','nvarchar')
then '('+cast(b.length/2 as varchar(10))+')'
when t1.name in ('decimal','numeric')
then '('+cast(b.prec as varchar(10))+','
+cast(b.scale as varchar(10))+')'
else ''
end,
allow_nulls=case when b.isnullable=1 then 'Y' else '' end,
default_value=case when c.[text] is not null
then substring(c.[text],2,len(c.[text])-2)
else ''
end,
id_property=
case when b.status=0x80
then '('+cast(ident_seed(a.name) as varchar)+','
+cast(ident_incr(a.name) as varchar)+')'
else ''
end,
formula=case when d.[text] is not null
then substring(d.[text],2,len(d.[text])-2)
else ''
end,
is_pk=case when xyz.keyno is not null then 'Y' else '' end,
pk_order=isnull(cast(xyz.keyno as varchar),'')
from sysobjects a inner join syscolumns b on a.id=b.id
left join systypes t1 on b.xusertype=t1.xusertype
left join syscomments c on b.cdefault=c.id
left join syscomments d on b.id=d.id and b.iscomputed=1
left join (select x.id,x.colid,x.keyno
from sysindexkeys x,sysindexes y,sysobjects z
where x.id=y.id and x.indid=y.indid
and x.id=z.parent_obj and y.name=z.name
and z.xtype='PK'
and INDEXPROPERTY(y.id,y.name,'IsAutoStatistics')=0
) xyz
on xyz.id=a.id and xyz.colid=b.colid
where a.xtype='U' and a.name<>N'dtproperties'
order by a.name,b.colorder
