SQL Server 2008获取一个表的字段,类型,长度,是否主键,是否为空,注释等信息
SELECT [表名]= case when a.colorder=1 then d. name else '' end , [表说明]= case when a.colorder=1 then isnull (f.value, '' ) else '' end , [字段序号]=a.colorder, [字段名]=a. name , [标识]= case when COLUMNPROPERTY( a.id,a. name , 'IsIdentity' )=1 then '√' else '' end , [主键]= case when exists( SELECT 1 FROM sysobjects where xtype= 'PK' and parent_obj=a.id and name in ( SELECT name FROM sysindexes WHERE indid in ( SELECT indid FROM sysindexkeys WHERE id = a.id AND colid=a.colid ))) then '√' else '' end , [类型]=b. name , [占用字节数]=a.length, [长度]=COLUMNPROPERTY(a.id,a. name , 'PRECISION' ), [小数位数]= isnull (COLUMNPROPERTY(a.id,a. name , 'Scale' ),0), [允许空]= case when a.isnullable=1 then '√' else '' end , [默认值]= isnull (e.text, '' ), [字段说明]= isnull (g.[value], '' ) FROM syscolumns a left join systypes b on a.xusertype=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 left join sys.extended_properties f on d.id=f.major_id and f.minor_id=0 --where d.name='TableName' --如果只查询指定表,加上此条件 order by a.id,a.colorder |
OR
select c. name as cname,c.prec AS Prec,c.isnullable AS isnullable, [IsPk]= 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 , [defaultval]= isnull (e.text, '' ), t. name as tname ,( select value from sys.extended_properties as ex where ex.major_id = c.id and ex.minor_id = c.colid) as notes from syscolumns as c inner join sys.tables as ta on c.id=ta.object_id inner join ( select name ,system_type_id from sys.types where name <> 'sysname' ) as t on c.xtype=t.system_type_id left join syscomments e on c.cdefault=e.id where ta. name = 'TestTb' order by c.colid |
分类:
数据库
【推荐】国内首个AI IDE,深度理解中文开发场景,立即下载体验Trae
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· 记一次.NET内存居高不下排查解决与启示
· 探究高空视频全景AR技术的实现原理
· 理解Rust引用及其生命周期标识(上)
· 浏览器原生「磁吸」效果!Anchor Positioning 锚点定位神器解析
· 没有源码,如何修改代码逻辑?
· 全程不用写代码,我用AI程序员写了一个飞机大战
· MongoDB 8.0这个新功能碉堡了,比商业数据库还牛
· 记一次.NET内存居高不下排查解决与启示
· DeepSeek 开源周回顾「GitHub 热点速览」
· 白话解读 Dapr 1.15:你的「微服务管家」又秀新绝活了