sql server 架构_在SQL Server中引入架构文档

sql server 架构

描述 (Description)

We often have a need to view object definitions in SQL Server, whether they be tables, triggers, or foreign keys. The built in tools are great for an object here and there, but are very cumbersome if you’re looking to generate create statements for a large number of objects.

我们经常需要在SQL Server中查看对象定义，无论它们是表，触发器还是外键。 内置工具非常适合在这里和那里的对象，但是如果您要为大量对象生成create语句，则它们非常麻烦。

We will be introducing (and reintroducing) many different system views that provide valuable information about objects within SQL Server. This will allow us to understand how to locate and use information about our data and then be able to perform extremely useful tasks, such as creating copies of our schema, validating correctness, or generating schema for testing purposes.

我们将介绍（并重新引入）许多不同的系统视图，这些视图提供有关SQL Server中对象的有价值的信息。 这将使我们了解如何查找和使用有关我们数据的信息，然后能够执行极其有用的任务，例如创建架构的副本，验证正确性或生成用于测试目的的架构。

背景和目的 (Background and Purpose)

Being able to quickly display the CREATE statement for an object can be extremely useful. Not only does this allow us to review our database schema, but it allows us to use that information to build out copies of some or all of those structures. Why would we ever want to do this? There are many good reasons, some of which I’ll list here:

能够快速显示对象的CREATE语句非常有用。 这不仅使我们可以查看数据库架构，还可以使我们使用该信息来构建某些或所有这些结构的副本。 我们为什么要这样做？ 有很多好的理由，我将在这里列出其中的一些理由：

• Quickly view the definition of a single object.
  快速查看单个对象的定义。
• Automated Schema Validation
  自动化架构验证
• Generate a creation script, to be used to build those objects elsewhere.
  生成创建脚本，以用于在其他位置构建那些对象。
• Use the creation scripts from multiple databases in order to compare/contrast objects.
  使用来自多个数据库的创建脚本来比较/对比对象。
• View all objects within a table in a single script.
  在单个脚本中查看表中的所有对象。
• View all or some objects in a database based on customized input.
  根据自定义输入查看数据库中的全部或某些对象。
• Generate creation scripts for use in source control.
  生成用于源代码管理的创建脚本。

SQL Server Management Studio allows you to right-click on any object that is viewable from the database tree and choose to generate a create statement from it, like this:

SQL Server Management Studio允许您右键单击在数据库树中可见的任何对象，然后选择从中生成一个create语句，如下所示：

The resulting TSQL is as follows:

生成的TSQL如下：

 
USE [AdventureWorks2014]
GO
 
/****** Object:  Table [Person].[Address]    Script Date: 5/8/2016 3:48:12 PM ******/
SET ANSI_NULLS ON
GO
 
SET QUOTED_IDENTIFIER ON
GO
 
CREATE TABLE [Person].[Address](
	[AddressID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
	[AddressLine1] [nvarchar](60) NOT NULL,
	[AddressLine2] [nvarchar](60) NULL,
	[City] [nvarchar](30) NOT NULL,
	[StateProvinceID] [int] NOT NULL,
	[PostalCode] [nvarchar](15) NOT NULL,
	[SpatialLocation] [geography] NULL,
	[rowguid] [uniqueidentifier] ROWGUIDCOL  NOT NULL,
	[ModifiedDate] [datetime] NOT NULL,
 CONSTRAINT [PK_Address_AddressID] PRIMARY KEY CLUSTERED 
(
	[AddressID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
 
GO
 
ALTER TABLE [Person].[Address] ADD  CONSTRAINT [DF_Address_rowguid]  DEFAULT (newid()) FOR [rowguid]
GO
 
ALTER TABLE [Person].[Address] ADD  CONSTRAINT [DF_Address_ModifiedDate]  DEFAULT (getdate()) FOR [ModifiedDate]
GO
 
ALTER TABLE [Person].[Address]  WITH CHECK ADD  CONSTRAINT [FK_Address_StateProvince_StateProvinceID] FOREIGN KEY([StateProvinceID])
REFERENCES [Person].[StateProvince] ([StateProvinceID])
GO
 
ALTER TABLE [Person].[Address] CHECK CONSTRAINT [FK_Address_StateProvince_StateProvinceID]
GO
 
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Primary key for Address records.' , @level0type=N'SCHEMA',@level0name=N'Person', @level1type=N'TABLE',@level1name=N'Address', @level2type=N'COLUMN',@level2name=N'AddressID'
GO
 
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'First street address line.' , @level0type=N'SCHEMA',@level0name=N'Person', @level1type=N'TABLE',@level1name=N'Address', @level2type=N'COLUMN',@level2name=N'AddressLine1'
GO
 
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Second street address line.' , @level0type=N'SCHEMA',@level0name=N'Person', @level1type=N'TABLE',@level1name=N'Address', @level2type=N'COLUMN',@level2name=N'AddressLine2'
GO
 
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Name of the city.' , @level0type=N'SCHEMA',@level0name=N'Person', @level1type=N'TABLE',@level1name=N'Address', @level2type=N'COLUMN',@level2name=N'City'
GO
 
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Unique identification number for the state or province. Foreign key to StateProvince table.' , @level0type=N'SCHEMA',@level0name=N'Person', @level1type=N'TABLE',@level1name=N'Address', @level2type=N'COLUMN',@level2name=N'StateProvinceID'
GO
 
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Postal code for the street address.' , @level0type=N'SCHEMA',@level0name=N'Person', @level1type=N'TABLE',@level1name=N'Address', @level2type=N'COLUMN',@level2name=N'PostalCode'
GO
 
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Latitude and longitude of this address.' , @level0type=N'SCHEMA',@level0name=N'Person', @level1type=N'TABLE',@level1name=N'Address', @level2type=N'COLUMN',@level2name=N'SpatialLocation'
GO
 
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.' , @level0type=N'SCHEMA',@level0name=N'Person', @level1type=N'TABLE',@level1name=N'Address', @level2type=N'COLUMN',@level2name=N'rowguid'
GO
 
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Default constraint value of NEWID()' , @level0type=N'SCHEMA',@level0name=N'Person', @level1type=N'TABLE',@level1name=N'Address', @level2type=N'CONSTRAINT',@level2name=N'DF_Address_rowguid'
GO
 
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Date and time the record was last updated.' , @level0type=N'SCHEMA',@level0name=N'Person', @level1type=N'TABLE',@level1name=N'Address', @level2type=N'COLUMN',@level2name=N'ModifiedDate'
GO
 
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Default constraint value of GETDATE()' , @level0type=N'SCHEMA',@level0name=N'Person', @level1type=N'TABLE',@level1name=N'Address', @level2type=N'CONSTRAINT',@level2name=N'DF_Address_ModifiedDate'
GO
 
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Street address information for customers, employees, and vendors.' , @level0type=N'SCHEMA',@level0name=N'Person', @level1type=N'TABLE',@level1name=N'Address'
GO
 
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Primary key (clustered) constraint' , @level0type=N'SCHEMA',@level0name=N'Person', @level1type=N'TABLE',@level1name=N'Address', @level2type=N'CONSTRAINT',@level2name=N'PK_Address_AddressID'
GO
 
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Foreign key constraint referencing StateProvince.StateProvinceID.' , @level0type=N'SCHEMA',@level0name=N'Person', @level1type=N'TABLE',@level1name=N'Address', @level2type=N'CONSTRAINT',@level2name=N'FK_Address_StateProvince_StateProvinceID'
GO
 

Wow…that is quite a bit of output for a single table. If all we needed was some information about this table, and we didn’t mind the extra output, then this would generally be adequate. If we were looking for schema creation scripts for an entire schema, database, or some other large segment of objects, then this approach would become cumbersome. Right-clicking a hundred times is not my idea of fun, nor is it something that can be easily automated.

哇...对于单个表来说，这是相当多的输出。 如果我们只需要有关此表的一些信息，而我们不介意多余的输出，则通常就足够了。 如果我们正在寻找用于整个模式，数据库或对象的其他较大部分的模式创建脚本，那么这种方法将变得很麻烦。 右键单击一百遍不是我的乐趣，也不是可以轻松自动化的东西。

Some of the output can be customized. For example, if I wanted to turn off the scripting of extended properties, I could do so via the SSMS options as follows:

一些输出可以定制。 例如，如果我想关闭扩展属性的脚本，可以通过如下的SSMS选项关闭：

While this menu allows for quite a bit of customization of scripting output, the idea of having to return to this menu whenever I would like to change what I am outputting does seem a bit slow. While clicking through menus is easy, it’s slow and manual, both attributes I don’t generally like to incorporate into my workday 🙂

尽管此菜单允许对脚本输出进行很多自定义，但是每当我想更改输出内容时都必须返回此菜单的想法似乎有点慢。 虽然通过菜单单击很容易，但是它很慢并且很手动，这两个属性我通常都不喜欢合并到我的工作日中🙂

I’m always looking for ways to automate and speed up clunky or slow processes—especially those that rely on any significant element of manual labor. Typically, the more we are doing by hand as part of routine processes, the greater the chance something will go wrong. We’re human, and while I consider myself an expert in right-clicking, if I had to do that a hundred times every Monday in order to validate some portion of schema, it’s unclear whether I would make a mistake or lose my mind first.

我一直在寻找方法来自动化和加速笨拙或缓慢的流程，尤其是那些依赖于任何重要体力劳动要素的流程。 通常，我们作为例行流程的一部分进行的工作越多，出现问题的机会就越大。 我们很人性化，虽然我认为自己是右键单击方面的专家，但是如果我每个星期一必须做一百次才能验证模式的某些部分，则尚不清楚我会犯错还是先失去理智。

Either way, I’d like to propose an alternative to all of these possibilities. Using data collected from system views, we can do all of this ourselves. Once we have sufficiently researched and gathered data from system views, we can create a stored procedure using those collection processes and automate everything into a stored procedure call with a handful of parameters.

无论哪种方式，我都想提出所有这些可能性的替代方案。 使用从系统视图收集的数据，我们可以自己完成所有这些操作。 一旦我们充分研究并从系统视图中收集了数据，就可以使用这些收集过程创建存储过程，并将所有内容自动化到带有少数参数的存储过程调用中。

What follows is an introduction to these views, how to use them in order to gather useful information about our database and the objects within.

接下来是对这些视图的介绍，以及如何使用它们来收集有关我们的数据库和其中的对象的有用信息。

使用系统元数据了解我们的数据库 (Using System Metadata to Understand our Database)

SQL Server provides a wide variety of system views, each of which provides a vast array of information about objects within SQL Server. These allow us to learn about table attributes, system settings, or view what sorts of schema exist in any database.

SQL Server提供了各种各样的系统视图，每个视图都提供了有关SQL Server中对象的大量信息。 这些使我们能够了解表属性，系统设置，或查看任何数据库中存在哪些类型的架构。

This analysis will focus on the primary structures that make up any database and that house our data: schemas, tables, constraints, indexes, and triggers. We’ll also throw in extended properties in order to illustrate our ability to learn about some of the less used (but potentially handy) components within SQL Server.

该分析将集中于构成任何数据库并存储我们的数据的主要结构：模式，表，约束，索引和触发器。 我们还将抛出扩展属性，以说明我们了解SQL Server中一些较少使用（但可能方便）的组件的能力。

模式与表格 (Schemas & Tables)

Schemas and tables are easy to understand. From within a database, we can view a list of schemas like this:

模式和表很容易理解。 在数据库中，我们可以查看如下模式列表：

 
SELECT
	*
FROM sys.schemas;
 

Running this query lists all schemas within our database. Schemas are useful for organizing database objects and/or applying more granular security to different types of data. When run on AdventureWorks, the results of the above query are:

运行此查询将列出数据库中的所有架构。 模式对于组织数据库对象和/或对不同类型的数据应用更精细的安全性很有用。 在AdventureWorks上运行时，上述查询的结果是：

The results are simply a list of schema names within the database. The view contains the schema name, its ID, and the ID of its owner. Database roles appear in the list as well. While their inclusion may seem confusing, eliminating them is easy when we join this view to other views of interest. This view contains all entities that are capable of owning others from the context of database schemas. Some of the schemas above are familiar, such as dbo (the default schema in SQL Server), and the AdventureWorks-specific ones, such as Sales, Purchasing, or HumanResources.

结果只是数据库中架构名称的列表。 该视图包含模式名称，其ID和其所有者的ID。 数据库角色也出现在列表中。 尽管将它们包括在内可能会造成混淆，但是当我们将此视图与其他感兴趣的视图结合在一起时，消除它们很容易。 该视图包含能够从数据库架构的上下文中拥有其他实体的所有实体。 上面的某些架构很熟悉，例如dbo （SQL Server中的默认架构），以及AdventureWorks特定的架构，例如Sales ， Purchasing或HumanResources 。

Tables represent our primary storage mechanism and are therefore very important to us. We can view lots of information about them as follows:

表代表了我们的主要存储机制，因此对我们非常重要。 我们可以查看有关它们的许多信息，如下所示：

 
SELECT
	*
FROM sys.tables
WHERE tables.is_ms_shipped = 0;
 

Adding the check on is_ms_shipped will filter out any system tables. If you’d like the full list of all tables, including system objects, feel free to comment out or omit this filter. The results are as follows:

在is_ms_shipped上添加检查将过滤掉所有系统表。 如果您想要所有表（包括系统对象）的完整列表，请随时注释掉或忽略此过滤器。 结果如下：

There are quite a few columns there, including another 2.5 pages worth that are off of the screen to the right! They include different bits of metadata that will be useful under a variety of circumstances. For the sake of our work here, we’ll simply stick to collecting table & schema names for those that are not system tables. We can join our two views above in order to list schemas and tables together:

那里有很多列，其中右边屏幕上还有另外2.5页值得！ 它们包含元数据的不同位，这些位元在各种情况下都将很有用。 为了在这里工作，我们将简单地坚持为不是系统表的表和模式名收集表和模式名。 我们可以将上面的两个视图结合在一起，以便将模式和表一起列出：

 
SELECT
	schemas.name AS SchemaName,
	tables.name AS TableName
FROM sys.schemas
INNER JOIN sys.tables
ON schemas.schema_id = tables.schema_id
WHERE tables.is_ms_shipped = 0
ORDER BY schemas.name, tables.name;
 

Schema_id can be used in order to join these views together and connect schemas to tables:

可以使用Schema_id来将这些视图连接在一起并将模式连接到表：

Adding aliases to each column proves useful, since the names of each are “name”, which is not a terribly descriptive way to differentiate between schema and table names. Later on in this article, as we return information about many other types of objects, aliasing them with friendly names will greatly improve readability and the ability to understand the results quickly & easily. Ordering by schema and table names also allows us to more easily browse through the results.

向每列添加别名被证明是有用的，因为每一个的名称都是“ name ”，这不是区分模式名和表名的非常描述性的方式。 在本文的后面，当我们返回有关许多其他类型对象的信息时，为它们加上友好的名称别名将大大提高可读性，并能够快速，轻松地理解结果。 按模式和表名排序也使我们可以更轻松地浏览结果。

列 (Columns)

Columns contain each attribute of a table. Understanding them is imperative to understanding the contents of a table and the types of data that we store there. The following query adds sys.columns to our existing query, which provides additional information about each column, the table they belong to, and the schema the table belongs to:

列包含表的每个属性。 了解它们对于了解表的内容和我们在其中存储的数据类型至关重要。 以下查询将sys.columns添加到我们现有的查询中，该查询提供有关每个列，它们所属的表以及该表所属的架构的其他信息：

 
SELECT
	schemas.name AS Schema_Name,
	tables.name AS Table_Name,
	columns.name AS Column_Name,
	ROW_NUMBER() OVER (PARTITION BY schemas.name, tables.name ORDER BY columns.column_id ASC) AS Ordinal_Position,
	columns.max_length AS Column_Length,
	columns.precision AS Column_Precision,
	columns.scale AS Column_Scale,
	columns.collation_name AS Column_Collation,
	columns.is_nullable AS Is_Nullable,
	columns.is_identity AS Is_Identity,
	columns.is_computed AS Is_Computed,
	columns.is_sparse AS Is_Sparse
FROM sys.schemas
INNER JOIN sys.tables
ON schemas.schema_id = tables.schema_id
INNER JOIN sys.columns
ON tables.object_id = columns.object_id;
 

Columns are joined to tables using the object_id of the table, which is referenced by any columns contained within. The query above returns much more than column names, including details about each column that are useful when figuring out what kind of data each contains. The results begin to paint a clearer picture of our data:

使用表的object_id将列连接到表，该表的object_id由其中包含的任何列引用。 上面的查询返回的信息远不止列名，还包括有关每列的详细信息，这些信息在确定每个数据包含哪种数据时很有用。 结果开始使我们的数据更加清晰：

The ordinal position tells us the column order, which is useful when inserting into a table, or determining the logical order for data. Other columns provide additional information, such as the column length, nullability, identity status, and more!

顺序位置告诉我们列的顺序，这在插入表或确定数据的逻辑顺序时很有用。 其他列提供其他信息，例如列长度，可为空性，身份状态等！

资料类型 (Data Types)

This is a great start, but we can learn more. Sys.types tells us more about the data type for each column, and can be joined directly to our previous query using user_type_id. The resulting type, when combined with length, precision, and scale, tell us exactly about a column’s data type and how it is defined:

这是一个很好的开始，但是我们可以了解更多。 Sys.types告诉我们有关每一列的数据类型的更多信息，并且可以使用user_type_id直接加入到我们之前的查询中。 将结果类型与长度，精度和小数位数结合使用时，可以准确地告诉我们有关列的数据类型及其定义方式：

 
SELECT
	schemas.name AS Schema_Name,
	tables.name AS Table_Name,
	columns.name AS Column_Name,
	UPPER(types.name) AS Column_Data_Type,
	columns.max_length AS Column_Length,
	columns.precision AS Column_Precision,
	columns.scale AS Column_Scale
FROM sys.schemas
INNER JOIN sys.tables
ON schemas.schema_id = tables.schema_id
INNER JOIN sys.columns
ON tables.object_id = columns.object_id
INNER JOIN sys.types
ON columns.user_type_id = types.user_type_id;
 

To prevent our results from getting too cluttered, I’ve removed some of the columns previously discussed:

为避免我们的结果过于混乱，我删除了前面讨论的一些列：

This additional information provides us with a familiar type name, such as DATETIME or INT, which may include customized user data types, such as NAME. We now have a basic understanding of what is in a table, and can now delve further into additional attributes.

此附加信息为我们提供了一个熟悉的类型名称，例如DATETIME或INT，其中可能包括定制的用户数据类型，例如NAME 。 现在，我们对表中的内容有了基本的了解，现在可以进一步研究其他属性。

身份列详细信息 (Identity Column Details)

One of the attributes previously identified in sys.columns was is_identity, which told us if a column was an identity or not. If it is, we also want to know the seed and increment of the column, which tell us how that identity will behave. This can be accomplished by joining sys.columns to sys.identity_columns:

先前在sys.columns中标识的属性之一是is_identity ，它告诉我们列是否为标识。 如果是的话，我们还想知道列的种子和增量，它们告诉我们该身份的行为。 这可以通过将sys.columns加入sys.identity_columns来完成：

 
SELECT
	schemas.name AS Schema_Name,
	tables.name AS Table_Name,
	columns.name AS Column_Name,
	UPPER(types.name) AS Column_Data_Type,
	CAST(identity_columns.seed_value AS BIGINT) AS Identity_Seed,
	CAST(identity_columns.increment_value AS BIGINT) AS Identity_Increment
FROM sys.schemas
INNER JOIN sys.tables
ON schemas.schema_id = tables.schema_id
INNER JOIN sys.columns
ON tables.object_id = columns.object_id
INNER JOIN sys.types
ON columns.user_type_id = types.user_type_id
LEFT JOIN sys.identity_columns
ON columns.object_id = identity_columns.object_id
AND columns.column_id = identity_columns.column_id;
 

Note that the join to sys.identity_columns requires the use of both object_id, which indicates the table it belongs to, and also column_id, which specifies the unique ID of the column within that table. In order to most easily and accurately reference any column uniquely, we must use both object_id and column_id. The results of the above query show the additional information added to the end of the result set:

请注意， 联接sys.identity_columns既需要使用object_id（表示该表所属的表），也要使用column_id （该表指定该表中列的唯一ID）。 为了最轻松，准确地唯一引用任何列，我们必须同时使用object_id和column_id 。 以上查询的结果显示了添加到结果集末尾的其他信息：

The results aren’t terribly exciting. IDENTITY(1,1) is the most common definition used for an identity column, but we’ve gained additional knowledge that will prove useful later on.

结果并不令人兴奋。 IDENTITY（1,1）是用于身份列的最常见定义，但是我们获得了更多知识，这些知识将在以后证明很有用。

默认约束 (Default Constraints )

A column can have at most a single default constraint associated with it. If one is defined, knowing its name and value are helpful in understanding the behavior of the column. A default often indicates a business rule or data need to ensure the column is not NULL, or is at least always populated with some important catchall value. We can gather this information from sys.default_constraints like this:

一列最多可以有一个与其关联的默认约束。 如果已定义，则了解其名称和值将有助于理解该列的行为。 默认值通常表示业务规则或数据需要确保该列不为NULL，或者至少始终填充有一些重要的catchall值。 我们可以像这样从sys.default_constraints收集这些信息：

 
SELECT
	schemas.name AS Schema_Name,
	tables.name AS Table_Name,
	columns.name AS Column_Name,
	UPPER(types.name) AS Column_Data_Type,
	default_constraints.name AS Default_Constraint_Name,
	UPPER(default_constraints.definition) AS Default_Constraint_Definition
FROM sys.schemas
INNER JOIN sys.tables
ON schemas.schema_id = tables.schema_id
INNER JOIN sys.columns
ON tables.object_id = columns.object_id
INNER JOIN sys.types
ON columns.user_type_id = types.user_type_id
LEFT JOIN sys.default_constraints
ON schemas.schema_id = default_constraints.schema_id
AND columns.object_id = default_constraints.parent_object_id
AND columns.column_id = default_constraints.parent_column_id;
 

Note that while this new view is joined on a schema, table, and column, the join on schema is unnecessary we are already are joining that view via sys.tables. Regardless, it is included for both documentation purposes and completeness. The results of the query show all columns, but if a default is defined, that information is also provided:

请注意，虽然此新视图是在模式，表和列上联接的，但在模式上的联接是不必要的，我们已经通过sys.tables联接了该视图。 无论如何，出于文档目的和完整性考虑将其包括在内。 查询的结果显示所有列，但是如果定义了默认值，还将提供该信息：

We can note a variety of defaults that take values of the current date, zero, and a new GUID unique identifier, though many other types can exist with whatever values you choose to assign. NULL indicates that a column has no default assigned to it. If we chose to join to sys.default_constraints using an INNER JOIN, then we would filter out all of the rows without defaults, leaving behind only the set of columns with default values defined.

我们可以注意到各种默认值，这些默认值采用当前日期，零和新的GUID唯一标识符的值，尽管可以存在许多其他类型，无论您选择分配什么值。 NULL表示没有分配默认值。 如果我们选择使用INNER JOIN加入sys.default_constraints ，那么我们将过滤掉所有没有默认值的行，只留下定义了默认值的那组列。

计算列 (Computed Columns)

Similar to default constraints, a column may only have a single computed definition associated with it. A computed column cannot be assigned values, and instead is automatically updated based on whatever definition is created for it. Information on this can be found in sys.computed_columns and joined back to sys.columns using object_id and column_id:

与默认约束类似，一列可能只有一个与之关联的计算定义。 无法为计算列分配值，而是根据为其创建的任何定义自动更新。 可以在sys.computed_columns中找到有关此信息，并使用object_id和column_id将其重新加入sys.columns ：

 
SELECT
	schemas.name AS Schema_Name,
	tables.name AS Table_Name,
	columns.name AS Column_Name,
	UPPER(types.name) AS Column_Data_Type,
	UPPER(computed_columns.definition) AS Computed_Column_Definition
FROM sys.schemas
INNER JOIN sys.tables
ON schemas.schema_id = tables.schema_id
INNER JOIN sys.columns
ON tables.object_id = columns.object_id
INNER JOIN sys.types
ON columns.user_type_id = types.user_type_id
LEFT JOIN sys.computed_columns
ON columns.object_id = computed_columns.object_id
AND columns.column_id = computed_columns.column_id;
 

This new view inherits all of the columns in sys.columns, adding a few additional pieces of information. Of these, we will focus on the definition, which tells us in TSQL how that column is populated:

这个新视图继承了sys.columns中的所有列，并添加了一些其他信息。 其中，我们将专注于定义，该定义在TSQL中告诉我们如何填充该列：

Since sys.computed_columns contains all of the information in sys.columns, it is not necessary to include sys.columns when also querying it if all we care about are columns with computed values defined on them. If we want to include all columns with the computed definition being optional, then the LEFT JOIN between them is required. Any column with no row in sys.computed_columns will result in a NULL in the above query, indicating that it does not have a computed column definition.

由于sys.computed_columns包含sys.columns中的所有信息，因此如果我们关心的只是在其上定义了计算值的列，则在查询时也不必包括sys.columns 。 如果我们想包括所有列，且计算的定义是可选的，则它们之间的LEFT JOIN是必需的。 sys.computed_columns中没有行的任何列将在上述查询中导致为NULL，表明它没有计算列定义。

索引和主键定义 (Indexes & Primary Key Definitions)

A table can only have one primary key associated with it, but this definition is important enough that we want to always capture it, regardless of what columns it is on, or if it is also a clustered index or not. Details about primary keys, as well as details on other indexes, can be found in sys.indexes. This view also holds data pertaining to other indexes on the table. Therefore, we can collect info on all indexes, including primary keys, in a single operation:

一个表只能有一个与之关联的主键，但是此定义足够重要，以至于我们希望始终捕获它，而不管它在哪一列上，也无论它是否是聚集索引。 可以在sys.indexes中找到有关主键的详细信息以及其他索引的详细信息。 该视图还保存与表上其他索引有关的数据。 因此，我们可以通过一次操作收集所有索引的信息，包括主键：

 
WITH CTE_INDEX_COLUMNS AS (
	SELECT
		INDEX_DATA.name AS Index_Name,
		SCHEMA_DATA.name AS Schema_Name,
		TABLE_DATA.name AS Table_Name,
		INDEX_DATA.is_unique,
		INDEX_DATA.has_filter,
		INDEX_DATA.filter_definition,
		INDEX_DATA.type_desc AS Index_Type,
		STUFF(( SELECT ', ' + columns.name + CASE WHEN index_columns.is_descending_key = 1 THEN ' DESC' ELSE ' ASC' END
				FROM sys.tables
				INNER JOIN sys.indexes
				ON tables.object_id = indexes.object_id
				INNER JOIN sys.index_columns
				ON indexes.object_id = index_columns.object_id
				AND indexes.index_id = index_columns.index_id
				INNER JOIN sys.columns
				ON tables.object_id = columns.object_id
				AND index_columns.column_id = columns.column_id
				WHERE INDEX_DATA.object_id = indexes.object_id
				AND INDEX_DATA.index_id = indexes.index_id
				AND index_columns.is_included_column = 0
				ORDER BY index_columns.key_ordinal
			FOR XML PATH('')), 1, 2, '') AS Index_Column_List,
			STUFF(( SELECT ', ' + columns.name
				FROM sys.tables
				INNER JOIN sys.indexes
				ON tables.object_id = indexes.object_id
				INNER JOIN sys.index_columns
				ON indexes.object_id = index_columns.object_id
				AND indexes.index_id = index_columns.index_id
				INNER JOIN sys.columns
				ON tables.object_id = columns.object_id
				AND index_columns.column_id = columns.column_id
				WHERE INDEX_DATA.object_id = indexes.object_id
				AND INDEX_DATA.index_id = indexes.index_id
				AND index_columns.is_included_column = 1
				ORDER BY index_columns.key_ordinal
			FOR XML PATH('')), 1, 2, '') AS Include_Column_List,
		Is_Primary_Key
	FROM sys.indexes INDEX_DATA
	INNER JOIN sys.tables TABLE_DATA
	ON TABLE_DATA.object_id = INDEX_DATA.object_id
	INNER JOIN sys.schemas SCHEMA_DATA
	ON TABLE_DATA.schema_id = SCHEMA_DATA.schema_id)
SELECT
	Index_Name,
	Schema_Name,
	Table_Name,
	is_unique,
	has_filter,
	filter_definition,
	Index_Type,
	Index_Column_List,
	ISNULL(Include_Column_List, '') AS Include_Column_List,
	Is_Primary_Key
FROM CTE_INDEX_COLUMNS
WHERE CTE_INDEX_COLUMNS.Index_Type <> 'HEAP';
 

This query collects basic index data, such as whether it is clustered, filtered, or a primary key. It also uses XML to pull the index column details into a comma-separated list, for easy use later on. Collecting all of this data at once is efficient and convenient, and avoids the need to return for column lists, or to check any properties of an index later on.

该查询收集基本索引数据，例如它是集群的，过滤的还是主键。 它还使用XML将索引列详细信息拉入以逗号分隔的列表中，以便以后使用。 一次收集所有这些数据既高效又方便，并且避免了以后需要返回列列表或以后检查索引的任何属性的麻烦。

While the query above appears complex, if we remove the XML necessary to parse the column list, the resulting query would only be a simple SELECT from sys.indexes, sys.tables, and sys.schemas. While we could do this initially, and then add the column lists later on, collecting all of this data right now will simplify our TSQL and improve performance as we won’t need to perform additional schema searches and joins to that data once this is complete.

虽然上面的查询看起来很复杂，但是如果我们删除解析列列表所必需的XML，则生成的查询将只是sys.indexes ， sys.tables和sys.schemas中的简单SELECT。 尽管我们最初可以这样做，然后再添加列列表，但是现在收集所有这些数据将简化TSQL并提高性能，因为一旦完成，我们将无需执行其他模式搜索并连接到该数据。

The results of the above query look like this:

以上查询的结果如下所示：

We omit HEAP from the results as they are not needed for explicit documentation of tables as they are automatically implied in the heap’s definition. We get a fairly wide result set back, but it provides us everything we need to understand an index and its purpose and usage.

我们从结果中省略了HEAP，因为对于表的显式文档来说不需要它们，因为它们会自动隐含在堆的定义中。 我们得到了相当广泛的结果，但是它为我们提供了理解索引及其目的和用法所需的一切。

外键 (Foreign Keys)

Foreign keys also represent column lists in one table that reference columns in a target table. We can view basic information on a foreign key using the system view sys.foreign_keys:

外键还表示一个表中的列列表，该列表引用目标表中的列。 我们可以使用系统视图sys.foreign_keys查看有关外键的基本信息：

 
SELECT
	foreign_keys.name AS Foreign_Key_Name,
	schemas.name AS Foreign_Key_Schema_Name,
	tables.name AS Foreign_Key_Table_Name
FROM sys.foreign_keys
INNER JOIN sys.tables
ON tables.object_id = foreign_keys.parent_object_id
INNER JOIN sys.schemas
ON schemas.schema_id = tables.schema_id;
 

This query returns a list of foreign keys and the source/target tables referenced by it:

此查询返回外键列表以及它引用的源/目标表：

This is straight-forward, but we also want to collect the column lists from source and target tables in order to correctly include them in our data. A foreign key is often a relationship between a single column in one table and its corresponding column in another table, but could exist between groups of columns. As a result, we must write our TSQL to be able to handle either scenario. For now, let’s look at a list of columns in which a foreign key is defined by a row per column in the result set:

这很简单，但是我们也想从源表和目标表中收集列列表，以便将它们正确地包含在我们的数据中。 外键通常是一个表中的单个列与另一表中的对应列之间的关系，但可能存在于各组列之间。 结果，我们必须编写我们的TSQL才能处理任何一种情况。 现在，让我们看一下列列表，其中结果集中每列的一行定义了一个外键：

 
SELECT
	foreign_keys.name AS Foreign_Key_Name,
	FOREIGN_KEY_TABLE.name AS Foreign_Key_Table_Name,
	FOREIGN_KEY_COLUMN.name AS Foreign_Key_Column_Name,
	REFERENCED_TABLE.name AS Referenced_Table_Name,
	REFERENECD_COLUMN.name AS Referenced_Column_Name
FROM sys.foreign_key_columns
INNER JOIN sys.foreign_keys
ON foreign_keys.object_id = foreign_key_columns.constraint_object_id
INNER JOIN sys.tables FOREIGN_KEY_TABLE
ON foreign_key_columns.parent_object_id = FOREIGN_KEY_TABLE.object_id
INNER JOIN sys.columns as FOREIGN_KEY_COLUMN
ON foreign_key_columns.parent_object_id = FOREIGN_KEY_COLUMN.object_id 
AND foreign_key_columns.parent_column_id = FOREIGN_KEY_COLUMN.column_id
INNER JOIN sys.columns REFERENECD_COLUMN
ON foreign_key_columns.referenced_object_id = REFERENECD_COLUMN.object_id
AND foreign_key_columns.referenced_column_id = REFERENECD_COLUMN.column_id
INNER JOIN sys.tables REFERENCED_TABLE
ON REFERENCED_TABLE.object_id = foreign_key_columns.referenced_object_id
ORDER BY FOREIGN_KEY_TABLE.name, foreign_key_columns.constraint_column_id; 
 

Sys.foreign_key_columns tells us the column relationships. From there, we need to join sys.tables and sys.columns twice: Once for the parent table and another for the foreign key table. Combining this information allows us to understand what columns reference and are referenced by any foreign key. If a foreign key has multiple columns participating in it, then it will be represented as multiple rows in the result set:

Sys.foreign_key_columns告诉我们列关系。 从那里，我们需要两次连接sys.tables和sys.columns ：一次用于父表，另一次用于外键表。 结合这些信息，我们可以了解哪些列引用了任何外键引用。 如果外键包含多个列，那么它将在结果集中表示为多行：

Since we need to worry about two sets of data, collecting it takes a bit more work, but much of it is a duplication of concepts that we have already discussed previously.

由于我们需要担心两组数据，因此收集数据需要花费更多的工作，但其中大部分是我们之前已经讨论过的概念的重复。

检查约束 (Check Constraints)

Check constraints are relatively simple to collect information on. Since they are stored with their entire definitions intact, there is no need to query column metadata in order to construct them. Sys.check_constraints can be queried in order to view info on check constraints:

检查约束相对容易收集信息。 由于它们是完整保留完整定义的，因此不需要查询列元数据来构造它们。 可以查询Sys.check_constraints以便查看有关检查约束的信息：

 
SELECT
	schemas.name AS Schema_Name,
	tables.name AS Table_Name,
	check_constraints.name AS Check_Constraint_Name,
	check_constraints.is_not_trusted AS With_Nocheck,
	check_constraints.definition AS Check_Constraint_Definition
FROM sys.check_constraints
INNER JOIN sys.tables
ON tables.object_id = check_constraints.parent_object_id
INNER JOIN sys.schemas
ON tables.schema_id = schemas.schema_id;
 

Selecting the is_not_trusted column allows us to validate if the constraint was created with NOCHECK or not. The definition contains the exact constraint details as they were entered when it was created. The results are easy to read and understand:

选择is_not_trusted列使我们可以验证是否使用NOCHECK创建了约束。 该定义包含创建时输入的确切约束详细信息。 结果易于阅读和理解：

The definition itself may be complex, but our efforts to collect this information are the same, regardless of how intricate the check constraint it.

定义本身可能很复杂，但是无论检查约束多么复杂，我们收集此信息的工作都是相同的。

扳机 (Triggers)

Triggers are stored in a very similar manner to check constraints. The only difference is that their definition is included in sys.sql_modules, whereas the trigger name and object information are stored in sys.triggers. Despite there being two tables involved, we can collect data on them in the same manner as with check constraints:

触发器以非常相似的方式存储以检查约束。 唯一的区别是它们的定义包含在sys.sql_modules中 ，而触发器名称和对象信息存储在sys.triggers中 。 尽管涉及两个表，但我们可以按照与检查约束相同的方式收集有关它们的数据：

 
SELECT
	schemas.name AS Schema_Name,
	tables.name AS Table_Name,
	sql_modules.definition AS Trigger_Definition
FROM sys.triggers
INNER JOIN sys.sql_modules
ON triggers.object_id = sql_modules.object_id
INNER JOIN sys.tables
ON triggers.parent_id = tables.object_id
INNER JOIN sys.schemas
ON schemas.schema_id = tables.schema_id;
 

Note that while we join to sys.triggers, we do not return any columns from that view. That is because the trigger definition provides all details of a trigger that would be found in a CREATE TRIGGER statement. Sys.sql_modules contains details about a variety of objects within SQL Server, such as stored procedures, triggers, and functions. Definitions within this view are all provided in their entirety, including the CREATE statement. As a result, there is no need to query for additional metadata, such as if the trigger is INSTEAD OF or AFTER, or if it is on UPDATE, DELETE, or INSERT. The results of the above query are as follows:

请注意，当我们加入sys.triggers时 ，我们不会从该视图返回任何列。 这是因为触发器定义提供了在CREATE TRIGGER语句中可以找到的触发器的所有详细信息。 Sys.sql_modules包含有关SQL Server中各种对象的详细信息，例如存储过程，触发器和函数。 此视图中的定义全部完整提供，包括CREATE语句。 结果，无需查询其他元数据，例如触发器是INSTEAD OF还是AFTER，或者是在UPDATE，DELETE或INSERT上。 以上查询的结果如下：

The results are far more simple than we would have expected! The entire definition is returned in a single column, which makes our work quite easy!

结果比我们预期的要简单得多！ 整个定义都在单个列中返回，这使我们的工作非常轻松！

扩展属性 (Extended Properties)

Extended properties are a bit odd in terms of definition and usage. They can be linked to many different objects in SQL Server, such as tables, columns, or constraints. As a result, we need to not only collect their definition, but also the object they relate to. This is a feature that not everyone uses, but is a good example of how even the more oddball parts of SQL Server can be documented if necessary.

扩展属性在定义和用法方面有些奇怪。 它们可以链接到SQL Server中的许多不同对象，例如表，列或约束。 结果，我们不仅需要收集它们的定义，还需要收集它们所涉及的对象。 这不是每个人都使用的功能，但是它是一个很好的示例，说明如有必要，甚至可以记录SQL Server的更多奇怪部分。

Sys.extended_properties contains all of the basic information about an extended property. The major_id and minor_id within the view provide us with information on what object the property references. Since we do not know precisely what type of object any extended property may reference up front, we need to LEFT JOIN all possible targets in order to collect a complete result set:

Sys.extended_properties包含有关扩展属性的所有基本信息。 视图中的major_id和minor_id为我们提供了有关属性引用的对象的信息。 由于我们不确切知道任何扩展属性可以预先引用什么类型的对象，因此需要LEFT JOIN所有可能的目标才能收集完整的结果集：

 
SELECT
	Child.type_desc AS Object_Type,
	extended_properties.name AS Extended_Property_Name,
	CAST(extended_properties.value AS NVARCHAR(MAX)) AS Extended_Property_Value,
	schemas.name AS Schema_Name,
	Child.name AS Object_Name,
	Parent.name AS Parent_Object_Name,
	columns.name AS Parent_Column_Name,
	indexes.name AS Index_Name
FROM sys.extended_properties
INNER JOIN sys.objects Child
ON extended_properties.major_id = Child.object_id
INNER JOIN sys.schemas
ON schemas.schema_id = Child.schema_id
LEFT JOIN sys.objects Parent
ON Parent.object_id = Child.parent_object_id
LEFT JOIN sys.columns
ON Child.object_id = columns.object_id
AND extended_properties.minor_id = columns.column_id
AND extended_properties.class_desc = 'OBJECT_OR_COLUMN'
AND extended_properties.minor_id <> 0
LEFT JOIN sys.indexes
ON Child.object_id = indexes.object_id
AND extended_properties.minor_id = indexes.index_id
AND extended_properties.class_desc = 'INDEX'
WHERE Child.type_desc IN ('CHECK_CONSTRAINT', 'DEFAULT_CONSTRAINT', 'FOREIGN_KEY_CONSTRAINT', 'PRIMARY_KEY_CONSTRAINT', 'SQL_TRIGGER', 'USER_TABLE')
ORDER BY Child.type_desc ASC;
 

In this query, sys.extended_properties forms the base table. Sys.objects and sys.schemas are connected using an INNER JOIN, as their metadata will apply to all properties, regardless of type. From here, the remaining joins allow us to gather additional information about the object referenced in sys.objects. The WHERE clause limits the targets that we are interested in to the types of objects that we have discussed thus far (constraints, triggers, and columns).

在此查询中， sys.extended_properties构成基本表。 Sys.objects和sys.schemas使用INNER JOIN连接，因为它们的元数据将应用于所有属性，而与类型无关。 从这里，其余的联接使我们能够收集有关sys.objects中引用的对象的其他信息。 WHERE子句将我们感兴趣的目标限制为到目前为止我们讨论过的对象的类型（约束，触发器和列）。

The results of the above query will look like this:

以上查询的结果将如下所示：

The results tell us the name of the extended property, the type of object it references, information about that object, and the text stored in the extended property itself. Microsoft wasn’t terribly creative and named all extended properties “MS_Description”. You can name yours whatever you want, though, such as in this dinosaur table that I’ve created:

结果告诉我们扩展属性的名称，它引用的对象的类型，有关该对象的信息以及扩展属性本身中存储的文本。 微软并不是非常有创造力，并且将所有扩展属性都命名为“ MS_Description”。 不过，您可以随便命名，例如在我创建的这张恐龙表中：

结论 (Conclusion)

System views provide a wealth of information about our data, how it is stored, and the constraints that we place on it. Using these views, we can quickly gather information about objects that are important to us, such as indexes or foreign keys. With this information, we can reconstruct our schema in a format that will assist in schema duplication, development and QA, and schema comparison.

系统视图提供了大量有关我们的数据，如何存储数据以及我们对其施加的约束的信息。 使用这些视图，我们可以快速收集有关对我们很重要的对象的信息，例如索引或外键。 有了这些信息，我们可以以一种有助于架构复制，开发和QA以及架构比较的格式来重构架构。

In our next article, Creating the perfect schema documentation script, we will take everything we have discussed here and combine it into a script that will greatly improve our ability to document and understand a database and its structure.

在下一篇文章“ 创建完美的模式文档脚本”中 ，我们将采用此处讨论的所有内容，并将其组合为脚本，这将大大提高我们记录和理解数据库及其结构的能力。

参考资料和进一步阅读 (References and Further Reading)

Some of the system views discussed here were also introduced in a previous 2-part article about searching SQL Server. If desired, we could combine these scripts such that the search script also returned the definition, as well. This could be a very efficient (and fun) way to find objects and their definition based on a keyword search:
Searching SQL Server made easy – Searching catalog views
Searching SQL Server made easy – Building the perfect search script

在前面有关搜索SQL Server的两部分文章中，还介绍了此处讨论的一些系统视图。 如果需要，我们可以组合这些脚本，以便搜索脚本也返回定义。 这可能是一种基于关键字搜索来找到对象及其定义的非常有效（且有趣）的方法：
轻松搜索SQL Server –搜索目录视图
轻松搜索SQL Server –构建完美的搜索脚本

Options are documented for the built-in SQL Server scripting here:
Generate SQL Server Scripts Wizard (Choose Script Options Page)

在此处记录了用于内置SQL Server脚本的选项：
生成SQL Server脚本向导（选择脚本选项页）

Some basic instructions on this process can be found here:
Generate Scripts (SQL Server Management Studio)

有关此过程的一些基本说明，可以在这里找到：
生成脚本（SQL Server Management Studio）

Lastly, information on catalog views, which provide the basis for this article, can be found here:
Catalog Views (Transact-SQL)

最后，可以在这里找到有关目录视图的信息，这些信息提供了本文的基础：
目录视图（Transact-SQL）

翻译自: https://www.sqlshack.com/introducing-schema-documentation-in-sql-server/

sql server 架构