小强

导航

< 2025年2月 >
26 27 28 29 30 31 1
2 3 4 5 6 7 8
9 10 11 12 13 14 15
16 17 18 19 20 21 22
23 24 25 26 27 28 1
2 3 4 5 6 7 8

统计

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

posted on   搬砖狗-小强  阅读(141)  评论(0编辑  收藏  举报

相关博文:
阅读排行:
· DeepSeek “源神”启动!「GitHub 热点速览」
· 微软正式发布.NET 10 Preview 1:开启下一代开发框架新篇章
· C# 集成 DeepSeek 模型实现 AI 私有化(本地部署与 API 调用教程)
· DeepSeek R1 简明指南:架构、训练、本地部署及硬件要求
· 2 本地部署DeepSeek模型构建本地知识库+联网搜索详细步骤
点击右上角即可分享
微信分享提示