SQL 获取基本的SQL Server表结构信息

在本文中，我们将介绍如何通过使用SQL来获取基本的SQL Server表结构信息。通过查询表的结构信息，我们可以了解表中包含的列，数据类型，约束以及索引等详细信息。这对于数据库开发和维护非常重要。

阅读更多：SQL 教程

1. 获取表的列信息

要获取表的列信息，我们可以使用sp_columns存储过程。该存储过程返回表的列信息，包括列名、数据类型、长度、是否允许为空等。

EXEC sp_columns '表名';

例如，要获取名为Employees的表的列信息，我们可以使用以下查询：

EXEC sp_columns 'Employees';

这将返回一个结果集，其中包含Employees表的列信息。

2. 获取表的主键信息

要获取表的主键信息，我们可以查询系统视图sys.key_constraints和sys.index_columns。通过连接这两个表，我们可以获取表的主键列以及主键的名称。

SELECT kc.name AS '主键名称', c.name AS '列名'
FROM sys.key_constraints AS kc
JOIN sys.index_columns AS ic ON kc.parent_object_id = ic.object_id AND kc.unique_index_id = ic.index_id
JOIN sys.columns AS c ON ic.object_id = c.object_id AND ic.column_id = c.column_id
WHERE kc.type = 'PK'
AND OBJECT_NAME(kc.parent_object_id) = '表名';

例如，要获取名为Employees的表的主键信息，我们可以使用以下查询：

SELECT kc.name AS '主键名称', c.name AS '列名'
FROM sys.key_constraints AS kc
JOIN sys.index_columns AS ic ON kc.parent_object_id = ic.object_id AND kc.unique_index_id = ic.index_id
JOIN sys.columns AS c ON ic.object_id = c.object_id AND ic.column_id = c.column_id
WHERE kc.type = 'PK'
AND OBJECT_NAME(kc.parent_object_id) = 'Employees';

这将返回一个结果集，其中包含Employees表的主键信息。

3. 获取表的外键信息

要获取表的外键信息，我们可以查询系统视图sys.foreign_keys和sys.foreign_key_columns。通过连接这两个表，我们可以获取表的外键列、主键表、主键列等详细信息。

SELECT f.name AS '外键名称', c.name AS '列名',
       OBJECT_NAME(f.referenced_object_id) AS '主键表名', cc.name AS '主键列名'
FROM sys.foreign_keys AS f
JOIN sys.foreign_key_columns AS fc ON f.object_id = fc.constraint_object_id
JOIN sys.columns AS c ON fc.parent_object_id = c.object_id AND fc.parent_column_id = c.column_id
JOIN sys.columns AS cc ON fc.referenced_object_id = cc.object_id AND fc.referenced_column_id = cc.column_id
WHERE OBJECT_NAME(f.parent_object_id) = '表名';

例如，要获取名为Employees的表的外键信息，我们可以使用以下查询：

SELECT f.name AS '外键名称', c.name AS '列名',
       OBJECT_NAME(f.referenced_object_id) AS '主键表名', cc.name AS '主键列名'
FROM sys.foreign_keys AS f
JOIN sys.foreign_key_columns AS fc ON f.object_id = fc.constraint_object_id
JOIN sys.columns AS c ON fc.parent_object_id = c.object_id AND fc.parent_column_id = c.column_id
JOIN sys.columns AS cc ON fc.referenced_object_id = cc.object_id AND fc.referenced_column_id = cc.column_id
WHERE OBJECT_NAME(f.parent_object_id) = 'Employees';

这将返回一个结果集，其中包含Employees表的外键信息。

4. 获取表的索引信息

要获取表的索引信息，我们可以查询系统视图sys.indexes和sys.index_columns。通过连接这两个表，我们可以获取表的索引列、索引类型等详细信息。

SELECT i.name AS '索引名称', c.name AS '列名'
FROM sys.indexes AS i
JOIN sys.index_columns AS ic ON i.object_id = ic.object_id AND i.index_id = ic.index_id
JOIN sys.columns AS c ON ic.object_id = c.object_id AND ic.column_id = c.column_id
WHERE OBJECT_NAME(i.object_id) = '表名';

例如，要获取名为Employees的表的索引信息，我们可以使用以下查询：

SELECT i.name AS '索引名称', c.name AS '列名'
FROM sys.indexes AS i
JOIN sys.index_columns AS ic ON i.object_id = ic.object_id AND i.index_id = ic.index_id
JOIN sys.columns AS c ON ic.object_id = c.object_id AND ic.column_id = c.column_id
WHERE OBJECT_NAME(i.object_id) = 'Employees';

这将返回一个结果集，其中包含Employees表的索引信息。