探索 SQL Server 上的数据库架构

引言

如果不了解数据库架构，您能做的很少。SQL Server Management Studio 有一个树状视图，可以查看数据库中的表、视图和其他对象，但在该列表中向下钻取以查找您感兴趣的信息可能既麻烦又耗时。在这里，我将向您展示几种获取信息的方法。我们将了解 SSMS 的可能性、如何从 INFORMATION_SCHEMA 视图中提取数据，以及如何使用 Red Gate SQL Prompt 快速查看信息。您可能希望下载 SQL Prompt 的免费试用版并跟着操作。

INFORMATION_SCHEMA 视图

在看一些例子之前，我应该更多地解释一下 INFORMATION_SCHEMA 视图。它们是 SQL92 标准的一部分，并首次在 SQL Server 7.0 中引入。因为它们是 SQL 标准的一部分，所以它们有时使用通用术语，而不是 SQL Server 其他地方使用的术语。最重要的区别是数据库被称为 Catalog。

INFORMATION_SCHEMA 视图包含每个数据库的架构信息。它们只包括 SQL 标准涵盖的区域，因此它们不包括许多 SQL Server 功能。对于这些功能，您必须查看系统表。INFORMATION_SCHEMA 视图有两个优点。与系统表不同，它们保证在服务器版本之间不会发生重大变化，因此您使用它们编写的任何脚本在下一个服务包发布时都不会中断。它们还受各种不同数据库引擎的支持，包括 SQL Server、MySQL 和 PostgreSQL。

查找所有表和视图

当我试图理解一个新数据库时，我通常从浏览所有可用表和视图的列表开始。在 Management Studio 中，您可以通过使用对象资源管理器树视图并查看您感兴趣的数据库下“表”和“视图”下的列表，或者通过使用对象资源管理器详细信息选项卡进行相同操作来查看此信息。除了最小的数据库之外，详细信息选项卡是首选。您还可以将表的名称拖到查询窗口中以将其插入到脚本中。

您可以使用以下方法从 INFORMATION_SCHEMA 提取表和视图的列表：

SELECT * FROM INFORMATION_SCHEMA.TABLES ORDER BY TABLE_TYPE

这将显示当前数据库中所有表和视图的列表。表的类型为 BASE TABLE；视图的类型为 VIEW。您可以通过过滤 TABLE_SCHEMA 列将此列表限制到特定的架构。

要使用 SQL Prompt 显示所有表的列表，请在查询编辑器窗格中键入 SELECT * FROM 。候选列表将显示所有表和视图的列表。如果您希望只过滤以查看表或只查看视图，可以使用列表下方的过滤器图标。也可以在空窗口中按 Ctrl+Space 调出此列表。这是获取表列表的最快方法，但您无法将列表复制到剪贴板。因此，如果您需要进行进一步处理，应该使用 INFORMATION_SCHEMA。

列列表

仅仅查看数据库中所有表的列表可能很有趣，但要进行任何有用的操作，我们需要了解更多关于其内容的信息：列。在 SSMS 中，可以通过对象资源管理器树中每个表下的“列”文件夹获取此信息。将显示每个数据类型、可空性以及列是否为键。您还可以将“列”文件夹本身从树拖到查询编辑器。这将插入表中所有列的逗号分隔列表。

在 SQL Server 中，也可以使用 sp_help 存储过程获取列列表。如果您将此过程传递给表的名称，它将返回一系列信息，包括键、索引、约束和所有列的列表。当您想快速了解单个表时，它非常有用。

不出所料，您可以从 INFORMATION_SCHEMA.COLUMNS 视图中获取列列表。此视图包含当前数据库中每列的一行。如果多个表中有相同名称的列，它们将显示为多个单独的行。如果您只对一个表中的列感兴趣，可以按表名进行过滤。例如（基于 AdventureWorks 数据库）

SELECT * FROM INFORMATION_SCHEMA.COLUMNS AS c WHERE c.TABLE_SCHEMA = 
    'Person' AND c.TABLE_NAME = 'Address'

返回的结果中大约有 20 列。最有趣的是 COLUMN_NAME、DATA_TYPE 和 COLUMN_DEFAULT，它们都包含您期望的内容。大多数其他列仅适用于特定的数据类型。例如，有一列指定 DATETIME 字段的精度。除非您正在查看 DATETIME，否则这将为 null。

结合使用 TABLES 和 COLUMNS 视图——以及一个使用 FOR XML PATH 进行字符串连接的技巧（仅限 SQL Server 2005+）——我们可以获得所有表的列表，每个表都带有一个逗号分隔的列列表。查询如下：

SELECT  t.TABLE_SCHEMA,
        t.TABLE_NAME,
        STUFF(( SELECT  ',' + COLUMN_NAME
                FROM    INFORMATION_SCHEMA.COLUMNS AS c
                WHERE   c.TABLE_SCHEMA = t.TABLE_SCHEMA
                        AND c.TABLE_NAME = t.TABLE_NAME
                ORDER BY c.COLUMN_NAME
              FOR
                XML PATH('')
              ), 1, 1, '') AS Columns
FROM    INFORMATION_SCHEMA.TABLES AS t

这里我们查找表列表，并对 COLUMNS 视图执行相关子查询，以查找该表中包含的所有列。FOR XML PATH(‘’) 将所有结果连接成一个单一值。STUFF 函数只是删除否则会出现在列表开头的逗号。

另一个常见任务是识别包含特定名称列的所有表：例如，所有具有 ContactID 列的表。这对于没有为所有关系设置外键的数据库很有用。从 COLUMNS 视图中提取此列表很简单。

SELECT TABLE_SCHEMA + '.' + TABLE_NAME FROM 
INFORMATION_SCHEMA.COLUMNS AS c WHERE COLUMN_NAME = 'ContactID'

SQL Prompt 以多种方式提供列信息。首先，如果您有一个通配符选择查询（例如 SELECT * FROM Person.Address），如果您将光标放在星号旁边并按 Tab 键，SQL Prompt 将插入该表中列的完整列表。您还可以将鼠标悬停在表名上以查看架构描述和表中包含的列列表。

键和关系

在表之间创建了外键关系的数据库中，它们提供了一种很好的方式来查看不同表之间的关系。在 SSMS 中，您可以在每个表下的“键”文件夹中查看与每个表关联的键。然而，这并不是特别有用，因为它不会在不打开单独对话框的情况下显示键引用了哪些表和列。INFORMATION_SCHEMA 视图允许我们提取此信息，但这有点尴尬。

INFORMATION_SCHEMA 视图存储有关三种类型约束的信息：检查约束、主键和外键。有些视图只存储有关某些类型约束的信息，有些则存储所有约束的信息。外键列表（且仅外键）保存在 INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS 视图中。对于每个外键，它具有键的完整名称（CONSTRAINT_CATALOG、CONSTRAINT_SCHEMA 和 CONSTRAINT_NAME）以及它引用的主键的完整名称（UNIQUE_CONSTRAINT_CATALOG、UNIQUE_CONSTRAINT_SCHEMA 和 UNIQUE_CONSTRAINT_NAME）。它不包含任何表或列信息：为此，您需要查看另一个视图。

表和列信息存储在 INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE 视图中。为了获取键的完整信息，我们需要将其连接两次：一次用于外键，一次用于它引用的主键。从数据库中提取所有外键关系的查询如下所示：

SELECT  ccu.TABLE_SCHEMA + '.' + ccu.TABLE_NAME + '.' + 
    ccu.COLUMN_NAME AS ForeignKeyColumn,
        ccu2.TABLE_SCHEMA + '.' + ccu2.TABLE_NAME + '.' + 
    ccu2.COLUMN_NAME AS PrimaryKeyColumn
FROM    INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS AS rc
JOIN    INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE AS ccu
        ON rc.CONSTRAINT_CATALOG = ccu.CONSTRAINT_CATALOG
        AND rc.CONSTRAINT_SCHEMA = ccu.CONSTRAINT_SCHEMA
        AND rc.CONSTRAINT_NAME = ccu.CONSTRAINT_NAME
JOIN    INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE AS ccu2
        ON rc.UNIQUE_CONSTRAINT_CATALOG = ccu2.CONSTRAINT_CATALOG
        AND rc.UNIQUE_CONSTRAINT_SCHEMA = ccu2.CONSTRAINT_SCHEMA
        AND rc.UNIQUE_CONSTRAINT_NAME = ccu2.CONSTRAINT_NAME

您还可以使用此功能查找所有引用特定列的外键的表。例如，要查找所有引用 Person.Contact.ContactID 的表，您可以使用以下内容（封装为存储过程以便于进一步使用）：

CREATE PROCEDURE GetReferencingTables (
	@TableSchema VARCHAR(MAX),
	@TableName VARCHAR(MAX),
	@ColumnName VARCHAR(MAX) = NULL
) AS
SELECT  ccu.TABLE_SCHEMA + '.' + ccu.TABLE_NAME AS ChildTable
FROM    INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS AS rc
JOIN    INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE AS ccu
        ON rc.CONSTRAINT_CATALOG = ccu.CONSTRAINT_CATALOG
        AND rc.CONSTRAINT_SCHEMA = ccu.CONSTRAINT_SCHEMA
        AND rc.CONSTRAINT_NAME = ccu.CONSTRAINT_NAME
JOIN    INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE AS ccu2
        ON rc.UNIQUE_CONSTRAINT_CATALOG = ccu2.CONSTRAINT_CATALOG
        AND rc.UNIQUE_CONSTRAINT_SCHEMA = ccu2.CONSTRAINT_SCHEMA
        AND rc.UNIQUE_CONSTRAINT_NAME = ccu2.CONSTRAINT_NAME
WHERE ccu2.TABLE_SCHEMA = @TableSchema
	AND ccu2.TABLE_NAME = @TableName
	AND (ccu2.COLUMN_NAME = @ColumnName OR @ColumnName IS NULL)
GO

-- Get all tables referencing Person.Contact.ContactID
EXECUTE GetReferencingTables 'Person', 'Contact', 'ContactID'

-- Get all tables referencing any column in Sales.Customer
EXECUTE GetReferencingTables 'Sales', 'Customer'

SQL Prompt 在您键入查询时提供连接信息。在您键入 JOIN 之后，它会向您显示其他表的列表，首先是那些可能与您正在选择的表连接的表。这是基于外键和匹配列名。然后，它会提示您两个表之间适当的完整连接条件。

您现在应该了解如何使用 SSMS、INFORMATION_SCHEMA 视图和 Red Gate SQL Prompt 探索数据库，以及何时适合使用它们。为了获得最佳结果，您应该拥有所有这三种工具，以便您可以根据手头的任务使用最合适的工具。

• MSDN 关于 INFORMATION_SCHEMA 的文档
• 更多信息和 SQL Prompt 的免费试用下载