[SQL Server]Sql Server根据表名获取表的所有列及属性

楼主^#

更多发布于：2012-12-13 15:10


	Sql Server根据表名获取表的所有列及属性实例一： www.atcpu.com select a.name columnname,c.name as typename,case when a.is_nullable =0 then 'Not Null' else 'Null' end as nullable,a.* from sys.columns a , sys.objects b, sys.types c where a.object_id= b.object_id and b.name='表名' and a.system_type_id=c.system_type_id orderby a.column_id 实例二： www.atcpu.com select c.name as [字段名],t.name as [字段类型] ,convert(bit,c.IsNullable) as [可否为空] ,convert(bit,case when exists(select 1 from sysobjects where xtype='PK' and parent_obj=c.id and name in ( select name from sysindexes where indid in( select indid from sysindexkeys where id = c.id and colid=c.colid))) then 1 else 0 end) as [是否主键] ,convert(bit,COLUMNPROPERTY(c.id,c.name,'IsIdentity')) as [自动增长] ,c.Length as [占用字节] ,COLUMNPROPERTY(c.id,c.name,'PRECISION') as [长度] ,isnull(COLUMNPROPERTY(c.id,c.name,'Scale'),0) as [小数位数] ,ISNULL(CM.text,'') as [默认值] ,isnull(ETP.value,'') AS [字段描述] --,ROW_NUMBER() OVER (ORDER BY C.name) AS [Row] from syscolumns c inner join systypes t on c.xusertype = t.xusertype left join sys.extended_properties ETP on ETP.major_id = c.id and ETP.minor_id = c.colid and ETP.name ='MS_Description' left join syscomments CM on c.cdefault=CM.id where c.id = object_id('表名') 来源 http://www.cnblogs.com/sydeveloper/archive/2012/12/10/2811544.html