SQL Service 查找表列中的属性

最新推荐文章于 2024-02-03 11:16:48 发布

xpi_01

最新推荐文章于 2024-02-03 11:16:48 发布

阅读量568

点赞数

CC 4.0 BY-SA版权

文章标签： sql

本文链接：https://blog.youkuaiyun.com/xpi_01/article/details/25894575

本文介绍了一种使用SQL查询特定表的结构信息的方法，包括字段编号、名称、类型、长度、是否为主键、是否为自增字段等详细属性。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

以下是查询sql service表中的列的属性，包括列名，类型，长度是否是主键，是否是自增字段。

SELECT d.name N'TableName',a.colorder N'FieldNumber',a.name N'FieldName', 
(case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 then '1'else '0' end) N'IsIdentifier',
(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) N'IsKeyField', b.name N'FieldType',a.length N'FieldSize', 
 COLUMNPROPERTY(a.id,a.name,'PRECISION') as N'FieldLength', 
 isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0) as N'DecimalDigits', 
 (case when a.isnullable=1 then '1'else '0' end) N'AllowNull', isnull(e.text,'') N'DefaultValue', 
 isnull(g.[value],'') AS N'FieldDescn' 
 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 object_name(a.id),a.colorder