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

本文提供了两种SQL查询示例,详细介绍了如何在Sql Server中根据表名获取所有列及其属性,包括列名、类型、是否允许为空、是否为主键、是否自动增长、占用字节数、长度、小数位数以及默认值和字段描述。
摘要由CSDN通过智能技术生成
实例一:             

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 order by a.column_id

实例二:


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('表名')

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值