学习数据字典是您可以做的最重要的事情之一，以便更容易地学习新的数据模型。学习新的数据模型通常是编写 SQL 查询最难的部分。数据库通常很少甚至没有文档，有时还有旧需求和失效系统的残余。如果数据库已经使用了一段时间，很有可能许多关系已经被删除，以适应脏数据或不断变化的数据需求。
所有这些都使得学习数据模型变得更加困难。
数据字典会有帮助。无论数据库更改的频率有多高，数据字典仍然是最新的。不管所有其他文档有多过时，数据字典都将是最新的，因为它由数据库本身保持最新。
数据字典是表和视图的集合，可以跟踪关于数据库的数百万条信息。在内部，数据库将这些信息用于性能调优、跟踪维护、构建执行计划等。我们可以用它来了解数据存储在哪里，并找出关键信息和关系。
当我在这里谈论数据字典时，我特别指的是存储在组成Information_Schema模式的每个数据库中的一组系统视图。还有其他可以使用的视图。每个数据库供应商都有系统视图，为其实现提供专有细节。它们也可能从一个版本变化到下一个版本，以适应每个版本中引入的新功能。
| 
如果您需要跟踪供应商和版本特定的元数据，您将需要直接使用系统表，但是正如您将看到的，我们可以获得许多有用的细节，同时将自己限制在Information_Schema中，这将有助于验证我们的流程。我们将要讨论的查询可以跨供应商移植，也可以跨同一个供应商的数据库版本移植。
| 
我们的讨论将集中在以下观点上:
Information_Schema.TablesInformation_Schema.ColumnsInformation_Schema.ViewsInformation_Schema.View_Table_UsageInformation_Schema.View_Column_UsageInformation_Schema.RoutinesInformation_Schema.Parameters
Tables视图拥有数据库中每个表的数据。这是我们获取数据库中表的列表的地方，或者更常见的是，满足嵌套问题的表的列表，例如具有特定的列。一旦我们探索了所有这些观点，我们将探索一些创造性的方法来使用这些信息。
Tables有以下几列:
Table_Catalog:数据库的名称。Table_Schema:表的所有者的名称。Table_Name:表的名称。Table_Type:表示是否为视图基表的指标。
Columns视图拥有数据库中每一列的数据。这个视图中有相当多的列。在某些情况下，您可能会关心这些列中的每一个，但就我们的目的而言，这些列中只有少数是相关的。
Table_Catalog:数据库的名称。Table_Schema:表的所有者的名称。Table_Name:表的名称。Column_Name:列的名称。Ordinal_Position:表中定义列的顺序。Is_Nullable:表示该列是否可以有空值的指标。Data_Type:该列数据类型的字符串。
该视图中的其余列用于收集有关数据类型的更多详细信息，如长度、精度范围等。
Views视图拥有数据库中定义的每个视图的数据。这与Tables视图非常相似，除了一个值得注意的增加。Views视图包括一个视图定义列，其中包含用于创建视图的 SQL。如果我们想要获得视图的定义，这可能会很有用。
View_Table_Usage视图对于任何视图定义中使用的每个表都有一条记录。这使得查询和获取任何视图中涉及的表列表以跟踪数据映射变得更加容易。
视图Information_Schema.View_Column_Usage对视图中暴露的每一列都有一条记录。这包括Table_Name和Column_Name这两个栏目，栏目来自国内。这使得很容易看到来自视图的数据来自哪里。
Information_Schema.Routines视图有数据库中定义的每个存储过程和存储函数的记录。这里有许多列提供了关于定义的可编程性对象的更多信息。出于学习数据模型的目的，我们并不真正关心这些列中的大多数。我们关心的栏目包括:
Specific_Name:正在定义的例程的名称。Routine_Type:标识正在定义的例程类型的指示器。Routine_Definition:此列保存用于定义例程的 SQL。
通常我们真正关心的是识别数据库中的存储过程。
Information_Schema.Parameters视图记录了任何程序中使用的每个参数。
Specific_Name将具有使用参数的例程的名称。Parameter_Name将具有正在定义的参数的名称。Ordinal_Position将具有参数预期的顺序。Parameter_Name将是正在定义的参数的名称。
我们还有很多列来定义参数的数据类型。
我们可以使用它来标识包含特定参数的所有存储过程，或者列出特定存储过程使用的所有参数。
当您第一次遇到一个新的数据库时，您可能会反复使用一些查询来了解您所拥有的东西。
代码清单 7-1:谁是所有者？
SELECT TABLE_SCHEMA ,
COUNT(1)
FROM INFORMATION_SCHEMA.TABLES
GROUP BY TABLE_SCHEMA;
这会告诉你谁是主人，他们每人有多少张桌子。
|
代码清单 7-2:金融拥有什么？
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA =
'Finance'
ORDER BY TABLE_NAME;
这将识别Finance拥有的所有表。如果每个功能区都有表所有者，这将为我们提供一个将在您系统的Finance模块中使用的表的列表。
有时您可能会在整个数据模型中使用一个列名。例如，在一个保单管理系统中，你可能会发现每个保单级别的表都会包含一个PolicyKey列。在抵押贷款发放系统中，每个贷款级别表都可能包含一个LoanKey列。这有助于识别或筛选出该级别的表。
代码清单 7-3:policy key 列在哪里？
SELECT t.TABLE_SCHEMA
,
t.TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES t
INNER JOIN INFORMATION_SCHEMA.COLUMNS ON COLUMNS.TABLE_NAME
= t.TABLE_NAME
AND COLUMNS.TABLE_SCHEMA = t.TABLE_SCHEMA
WHERE COLUMN_NAME =
'PolicyKey'
ORDER BY t.TABLE_NAME;
该查询应该标识数据库中的所有策略级表。
有时候你想做相反的事情。例如，在这种情况下，我们希望找到所有没有名为PolicyKey的列的表:
代码清单 7-4:哪些表没有 PolicyKey 列？
SELECT t.TABLE_SCHEMA
,
t.TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES t
LEFT OUTER JOIN INFORMATION_SCHEMA.COLUMNS
ON COLUMNS.TABLE_NAME = t.TABLE_NAME
AND COLUMNS.TABLE_SCHEMA = t.TABLE_SCHEMA
AND COLUMN_NAME = 'PolicyKey'
WHERE COLUMN_NAME IS null
ORDER BY t.TABLE_NAME;
这将为我们提供一个不在策略级别的所有表的列表。通常，这可能意味着这些表用于配置、管理或其他不在策略级别的事情。
现在，我们可以对此进行进一步扩展，以找到具有我们感兴趣的列组合的表:
代码清单 7-5:是否有任何表有一个外键和术语列组合？
SELECT t.TABLE_SCHEMA
,
t.TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES t
INNER JOIN INFORMATION_SCHEMA.COLUMNS P
ON P.TABLE_NAME
= t.TABLE_NAME
AND P.TABLE_SCHEMA
= t.TABLE_SCHEMA
INNER JOIN INFORMATION_SCHEMA.COLUMNS Term
ON Term.TABLE_NAME = t.TABLE_NAME
AND Term.TABLE_SCHEMA
= t.TABLE_SCHEMA
WHERE P.COLUMN_NAME
= 'LoanKey' AND term.COLUMN_NAME
= 'Term'
ORDER BY t.TABLE_NAME;
这里我们得到了一个既有LoanKey列又有Term列的表的列表。这可用于过滤掉用于配置期限选项或期限限制的任何表格，而仅报告贷款级别的Term参考。
我曾经处理过一个应用程序，其中一个地址的城市、州和邮政编码字段组合在CSZ字段中。不同的表会在一列中包含所有三个值。由于各种原因，这最终会有问题，所以我开始显式地寻找任何以CSZ结尾的列的表。
代码清单 7-6:有人把城市、州和邮政编码放在一列吗？
SELECT TABLE_NAME ,
COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME LIKE
'%CSZ';
另一个问题可能是处理常见或不一致的缩写。我经常在日期字段中看到这个。通常，人们在命名日期栏时会很有创意。比如我看到ClosingDt、FirstPaymentDueDte、RecissionDate都在同一个桌子上。我更喜欢总是拼出名字的组成部分，但不是每个人都同意我的观点。我们可以轻松找到您的数据库中使用的所有变体:
代码清单 7-7:我们如何命名日期列？
SELECT TABLE_NAME ,
COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE (DATA_TYPE = 'DateTime' or DATA_TYPE = 'Date')
AND COLUMN_NAME NOT
LIKE '%Date';
|
您可能遇到的另一个常见问题是应该一致的列的数据类型不一致。这可能会使与其他系统的集成变得复杂，或者在单个应用程序中报告时导致意外的结果。
代码清单 7-8:我们是否标准化了 City 列的长度？
SELECT TABLE_NAME ,
COLUMN_NAME, CHARACTER_MAXIMUM_LENGTH
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME LIKE
'%City';
您可能希望运行类似的查询来确认所有州列、邮政编码列、电话号码列等的长度一致。每当您看到多个字段名的公共组件时，请查看这些列，并确保它们具有一致的数据类型，或者您理解它们不一致的原因。
|
有几种数据类型已经被确定为过时，我们被警告它们可能会在未来被丢弃。跟踪这些列并确保新列不会出现在您的列表中是一个好主意。根据您的变更控制，您甚至可以将它们更新到更实用的标准。
代码清单 7-9:查找过时的类型
SELECT TABLE_NAME ,
COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE
DATA_TYPE IN ( 'text', 'ntext', 'image', 'datetime'
);
您可能会发现您想要避免的其他数据类型，因此您可能想要跟踪它们的使用情况。
代码清单 7-10:寻找要避免的类型
SELECT TABLE_NAME ,
COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE DATA_TYPE IN ( 'uniqueidentifier', 'ntext');
了解系统中使用的数据类型是很好的。第一次运行这样的查询，可能会发现很多不一致的地方。
代码清单 7-11:使用了什么数据类型？
SELECT DATA_TYPE ,
COUNT(1)
FROM INFORMATION_SCHEMA.COLUMNS
GROUP BY DATA_TYPE
ORDER BY DATA_TYPE;
您可能会发现如下结果:
| 数据类型 | 数数 |
|---|---|
| 少量 | Seventeen |
| 茶 | Two |
| 日期时间 | Thirty-four |
| 日期时间 2 | nine |
| 小数 | One hundred and twenty-four |
| 漂浮物 | one |
| （同 Internationalorganizations）国际组织 | Twenty-five |
| 恩查尔 | Two |
| nvarchar | five |
| 斯莫列特 | Two |
| Varbinary | one |
| 可变长字符串 | Two hundred and seventy |
结果集 7-1:正在使用的数据类型
这是很好的信息。你可以很快判断出你没有利用新的Date数据类型。你也可以很快判断出有人可能对各种数字数据类型感到困惑，例如NChar和NVarChar。
|
这将帮助您跟踪不一致的地方和标准化的进展。
我们在本章中看到，数据字典为数据库提供了一个很好的文档来源，即使所有其他文档早已过时，因为数据库本身会保持这些文档的最新状态。我们已经看到了如何使用这些信息，通过找出谁拥有数据库中的结构，并获得数据库中每个表的列表以及与每个表相关联的列，来快速了解数据库中的内容。通过搜索表中包含的关键列，我们可以轻松识别哪些表是引用数据，哪些表有事务数据。
我们还看到了一些如何检测数据库结构中可能导致问题的模式的例子。我们着眼于找到一种模式，其中城市、州和邮政编码数据组合在一个字段中，其中Date列的命名约定不一致，其中City等公共列的长度不一致，或者我们没有对数值的数据类型进行标准化。
当你面对一个数据模型并想从哪里开始时，有大量的信息可以帮助你快速了解这个数据模型。