alter procedure dbo.storedprocedure1
as
select (case when columnproperty( ss.id,ss.name,'isidentity')=1 then '1'else '0' end) n'標識',(case when (select count(*) from sysobjects where (name in (select name from sysindexes where (id = ss.id) and (indid in (select indid from sysindexkeys where (id = ss.id) and (colid in (select colid from syscolumns where (id = ss.id) and (name = ss.name))))))) and (xtype = 'pk'))>0 then '1' else '0' end) n'主鍵',ss.colorder n'字段序號', (case when ss.isnullable=1 then '1'else '0' end) n'允許空', s.name as n'表名',ss.name as n'字段名' ,t.name as n'數據類型' , isnull(columnproperty(ss.id,ss.name,'scale'),0) as n'小數位數', ss.length n'占用字節數', isnull(e.text,'') n'默認值', columnproperty(ss.id,ss.name,'precision') as n'長度',g.[value] as n'字段描述' from sysobjects s left join syscolumns ss on ss.id=s.id inner join systypes t on ( t.xtype=ss.xtype and t.name<>'sysname') inner join sysproperties g on (ss.id=g.id and ss.colid = g.smallid) inner join sysindexkeys sk on (sk.id=ss.id and sk.colid=1) left join syscomments e
on ss.cdefault=e.id where s.xtype='u' and s.name<>'dtproperties'
return
--by huntfox
