SQL查询主外键：借助INFORMATION_SCHEMA视图

本文主要探讨如何使用SQL中的INFORMATION_SCHEMA系统视图来获取数据库表的主外键信息，尤其强调自SQL Server 2005起，微软推荐使用该视图替代sys视图进行元数据查询。文章通过举例说明如何查询表的列信息，并展示了一段SQL代码，用于查询特定表（例如'Address'）的数据列详细属性，包括列名、数据类型、最大长度等。 在SQL中，INFORMATION_SCHEMA视图提供了一种标准的方法来查询数据库的元数据，不受数据库供应商的影响。要获取表的主外键信息，我们需要关注`INFORMATION_SCHEMA.KEY_COLUMN_USAGE`和`INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE`视图。这些视图包含有关表的所有约束，包括主键和外键。 首先，`INFORMATION_SCHEMA.COLUMNS`视图用于获取所有表的列信息。以下是一个示例查询： ```sql SELECT c.TABLE_SCHEMA, c.TABLE_NAME, c.COLUMN_NAME, c.DATA_TYPE, c.CHARACTER_MAXIMUM_LENGTH, c.COLUMN_DEFAULT, c.IS_NULLABLE, c.NUMERIC_PRECISION, c.NUMERIC_SCALE FROM INFORMATION_SCHEMA.COLUMNS c WHERE TABLE_NAME = 'Address' ``` 这段代码将返回'Address'表的所有列及其属性，如列的模式、列名、数据类型、最大长度、默认值、是否可为空以及数值精度和小数位数。 对于主键和外键信息，可以使用以下查询： ```sql SELECT kc.CONSTRAINT_NAME AS ConstraintName, kc.TABLE_SCHEMA AS SchemaName, kc.TABLE_NAME AS TableName, kc.COLUMN_NAME AS ColumnName, ccu.CONSTRAINT_NAME AS ReferencedConstraintName, ccu.TABLE_SCHEMA AS ReferencedSchemaName, ccu.TABLE_NAME AS ReferencedTableName, ccu.COLUMN_NAME AS ReferencedColumnName FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE kc LEFT JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE ccu ON kc.CONSTRAINT_NAME = ccu.CONSTRAINT_NAME WHERE (kc.CONSTRAINT_NAME LIKE 'PK%' OR kc.CONSTRAINT_NAME LIKE 'FK%') AND kc.TABLE_NAME = 'Address' ``` 这个查询将返回'Address'表中的主键（以'PK_'开头的约束名）和外键（以'FK_'开头的约束名），包括它们所涉及的列、引用的约束、引用的模式、引用的表以及引用的列名。 通过结合使用这些视图，我们可以全面了解表的结构，包括主键和外键关系，这对于数据库设计、数据迁移或数据分析都是非常有用的。虽然sys视图可能提供更深入或特定于数据库的信息，但在多数情况下，INFORMATION_SCHEMA视图已经足够满足获取表基本信息的需求。