SQL Server 查询表结构、主键与自增列指南

结果或目标：获取数据库表列表

若需列出指定数据库中的所有用户创建表，可查询系统对象表 sysobjects。以下示例以数据库 jsj01 为例，筛选类型为 'U'（User Table）的记录，并排除系统默认表。

SELECT name AS tablename 
FROM jsj01..sysobjects 
WHERE type='U' 
AND name NOT IN ('dtproperties');

真实操作顺序：查询表字段与数据类型

第1步：基础字段信息查询

目的：获取指定表的所有字段ID信息。
动作：查询 syscolumns 系统表，利用 object_id() 函数定位表。

-- docs为表名
SELECT * FROM syscolumns 
WHERE id = object_id('docs');

第2步：联合查询字段名与数据类型

目的：将 syscolumns 中的类型代码（xtype）映射到可读的数据类型名称。
入口：同时访问 syscolumns 和 systypes 两张系统表。
参数：关联条件为 syscolumns.xtype = systypes.xusertype，筛选目标表 ID。

-- syscolumns里字段‘xtype' 对应 systypes里的 ‘xusertype'
-- docs 为表名
SELECT a.name AS fieldname, b.name AS type 
FROM syscolumns AS a
JOIN systypes AS b ON a.xtype = b.xusertype
WHERE id = object_id('docs');

第3步：查询字段名、类型及备注说明

目的：在获取字段基本信息的同时，提取扩展属性中的注释说明。
注意：不同版本的 SQL Server 处理注释的函数略有差异。以下为兼容较旧版本及通用写法的示例，涉及跨库排序规则时需确保一致性。

-- docs为数据表名 ： 查询表字段、类型、说明
SELECT a.name AS fieldname, 
       b.name AS type, 
       c.value AS comment 
FROM syscolumns AS a
FULL JOIN systypes AS b ON a.xtype = b.xusertype
-- 此处调用扩展属性函数，2005及以上版本通常建议使用 sys.extended_properties
FULL JOIN ::fn_listextendedproperty(NULL, 'user', 'dbo', 'table', 'docs', 'column', DEFAULT) AS c 
ON a.name = c.objname COLLATE Chinese_PRC_CI_AS 
WHERE id = object_id('docs');

关键操作：查询主键与自增列

第4步：查询主键及外键信息

目的：识别表中的主键（PK）和外键（FK）约束。
入口：使用标准 INFORMATION_SCHEMA.KEY_COLUMN_USAGE 视图。
完成状态：返回包含约束名称和列名的结果集。若表中无主键，则返回空集；若存在组合主键，则返回多行记录。

-- jsj01 为数据库名，docs为表名
-- 如需仅查主键，可将 constraint_name like 'PK_%' 作为过滤条件
SELECT column_name AS primarykey, * 
FROM [jsj01].INFORMATION_SCHEMA.KEY_COLUMN_USAGE 
WHERE Table_name = 'docs' 
AND constraint_name LIKE 'fk_%'; -- 示例中查找外键，请根据实际需求修改匹配模式

第5步：查询自动增长（标识）列

目的：定位开启了 IDENTITY（自动增长）属性的列。
参数：在 syscolumns 中，检查 autoval 字段是否不为 NULL。

-- docs为表名
-- 注意：一个表通常只有一个自增列，若有则为唯一值，否则为空
SELECT a.name AS column_name, 
       b.name AS data_type
FROM syscolumns a,
     systypes b
WHERE a.id = object_id('docs') 
AND a.xtype = b.xusertype
AND a.autoval IS NOT NULL;

简要总结

以上介绍了在 SQL Server 中查询表结构、主键、外键及自增列的核心 SQL 语句。实际应用中，建议优先使用 INFORMATION_SCHEMA 视图以保证跨版本兼容性，或在需要深度元数据时使用 sys.* 系列系统视图。以上就是关于 SQL Server 查询表结构与属性指令的详细内容，更多关于数据库结构管理的资料请关注本站其它相关文章！