查找SQL数据表或视图中的字段属性信息
浏览数：14 /
时间：2015年06月12日
一、只支持表，非常牛逼的
SELECT a.name,(case when (SELECT count(*) FROM sysobjects WHERE (name in (SELECT name FROM sysindexes WHERE (id = a.id) AND (indid in (SELECT indid FROM sysindexkeys WHERE (id = a.id) AND (colid in (SELECT colid FROM syscolumns WHERE (id = a.id) AND (name = a.name))))))) AND (xtype = ‘PK‘))>0 then ‘1‘ else ‘0‘ end) isprimary, b.name [type],COLUMNPROPERTY(a.id,a.name,‘PRECISION‘) as [length],isnull(g.value,‘‘) as [description] FROM syscolumns a left join systypes b on a.xtype=b.xusertype inner join sysobjects d on a.id=d.id and d.xtype=‘U‘ and d.name<>‘dtproperties‘ left join syscomments e on a.cdefault=e.id left join sys.extended_properties g on a.id=g.major_id AND a.colid = g.minor_id where d.name =‘表明‘ order by a.id,a.colorder
二，一般牛逼，支持表
select object_name(id) as 表名 ,c.name as 字段名 ,t.name 数据类型 ,c.prec as 长度 from syscolumns c inner join systypes t on c.xusertype=t.xusertype where objectproperty(c.id,‘IsUserTable‘)=1 and c.id=object_id(‘表明‘)
三、一般牛逼，支持视图
select object_name(id) as 表名 ,c.name as 字段名 ,t.name 数据类型 ,c.prec as 长度 from syscolumns c inner join systypes t on c.xusertype=t.xusertype where objectproperty(c.id,‘IsView‘)=1 and c.id=object_id(‘V_ALLSay‘)
求大神非常牛逼的，既支持表也支持视图的sql
郑重声明：本站内容如果来自互联网及其他传播媒体，其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享，并不代表本站赞同其观点和对其真实性负责，也不构成任何其他建议。
