脚本文档_创建完美的架构文档脚本

最新推荐文章于 2024-03-17 18:11:01 发布

culuo4781

最新推荐文章于 2024-03-17 18:11:01 发布

阅读量813

点赞数

文章标签：数据库大数据编程语言 python 机器学习

原文链接：https://www.sqlshack.com/creating-the-perfect-schema-documentation-script/

版权

脚本文档

描述 (Description)

System views allow us to gain access to information about any objects within SQL Server, from tables to extended properties to check constraints. This information can be collected and used for many purposes, one being the need to document our objects without the need to click endlessly in the GUI or to incur an immense amount of manual work.

系统视图使我们能够访问有关SQL Server中任何对象的信息，从表到扩展属性以检查约束。可以收集这些信息并将其用于许多目的，其中之一是需要记录我们的对象，而无需在GUI中无休止地单击或进行大量的手工工作。

In our continuing effort to make good use of these views, we’ll take the data we previously collected in the previous article on Schema Documentation and use that information to build an easy-to-use and customizable solution that will allow us to efficiently reproduce, analyze, or model any database schema.

在我们继续努力利用这些视图的过程中，我们将使用先前在Schema Documentation上一篇文章中收集的数据，并使用这些信息来构建易于使用和可定制的解决方案，从而使我们能够有效地重现，分析或建模任何数据库模式。

目的简述 (Brief Recap of Purpose)

The stored procedure we are about to build will allow us to generate the CREATE statements for any tables you wish, including related objects, such as indexes, constraints, column metadata, triggers, and more.

我们将要构建的存储过程将使我们能够为所需的任何表生成CREATE语句，包括相关对象，例如索引，约束，列元数据，触发器等。

This is very handy when researching the structure of a table or when we are looking to quickly build a copy of a table in another location. For development, QA, or documentation, this could be a very useful tool! SQL Server comes with the ability to right-click on objects and script out their creation details, but this is a slow and cumbersome process. For customization, or the ability to view many objects, endless right-clicking is not very appealing.

在研究表的结构时或当我们希望在另一个位置快速构建表的副本时，这非常方便。对于开发，质量检查或文档，这可能是一个非常有用的工具！ SQL Server具有右键单击对象并编写其创建细节脚本的功能，但这是一个缓慢而繁琐的过程。对于自定义或查看许多对象的能力，无休止的右键单击不是很吸引人。

The results we come up with illustrate a few different ways to generate CREATE statements while allowing for customization to your heart’s content. If there are objects that you would like to include that we do not demo here, such as partitioning, encryption, or replication, feel free to add them into the script using similar techniques.

我们提出的结果说明了生成CREATE语句的几种不同方式，同时允许您自定义内容。如果您要包含的对象（例如分区，加密或复制）在此处未进行演示，请使用类似技术随意将其添加到脚本中。

The search script in its entirety is a bit long, and therefore is attached to this article as a .SQL file. This article focuses on how to build the script, and some decisions we make along the way. Please download and experiment with the full script, in addition to reviewing the info here on how to build and use it. This will greatly improve the experience of reading this article, as well as provide a useful tool for the future.

整个搜索脚本有点长，因此作为.SQL文件附在本文中。本文重点介绍如何构建脚本以及我们在此过程中做出的一些决策。除了在此处查看有关如何构建和使用它的信息外，请下载并尝试使用完整脚本。这将大大改善阅读本文的经验，并为将来提供有用的工具。

文档存储过程的结构 (The Structure of a Documentation Stored Procedure)

Building this stored procedure will require a bit of planning. The queries against system views will be similar to what we have previously done, but how we collect and use that data will be different than our experiences thus far. Our first questions to ask will involve parameters. What parts of a documentation proc should be customizable? This is very much based on opinion, but here is what I came up with:

构建此存储过程将需要一些计划。针对系统视图的查询将类似于我们之前所做的工作，但是我们如何收集和使用这些数据将不同于我们迄今为止的经验。我们要问的第一个问题将涉及参数。文档过程的哪些部分应可自定义？这很大程度上是基于观点，但这是我想出的：

Database Name: It’s unlikely we would want to script out schema for all databases on a server, nor does the prospect of writing dynamic SQL nested within dynamic SQL sound very enjoyable. We’ll pass in a single database name and script objects for that database only. 数据库名称 ：我们不太可能希望为服务器上的所有数据库编写模式脚本，也不太可能编写嵌套在动态SQL中的动态SQL的前景。我们将只为该数据库传递一个数据库名称和脚本对象。
Schema Name: Optionally, we can provide a schema name, which will restrict the schema build to only those objects within this specific schema. 模式名称 ：（可选）我们可以提供一个模式名称，它将模式构建仅限制于该特定模式内的那些对象。
Table Name: Optionally, we can provide a table name, which will restrict results to any tables with this name. 表名：可选地，我们可以提供一个表名，它将结果限制为具有该名称的任何表。
Print or Select results: This allows us to either print results, which is great for immediate copy/paste/use, and select results, which allows us to store the output in a table for posterity or future use. 打印或选择结果 ：这使我们既可以打印结果（非常适合立即复制/粘贴/使用），又可以选择结果（将结果存储在表格中以供后代或将来使用）。
Customize What to Display: We can create and set bits that allow us to determine what types of objects to script out. For our example, we’ll include a single bit that enables or disables the display of extended properties, which may not be something you wish to see in your output. 自定义要显示的内容 ：我们可以创建和设置一些位，以使我们可以确定要编写脚本的对象类型。对于我们的示例，我们将包括一个启用或禁用扩展属性显示的位，这可能不是您希望在输出中看到的。

Further customization is up to the user. There are many other ways to expand or restrict the result set in order to meet your own business or database scripting needs.

进一步的定制取决于用户。还有许多其他方法可以扩展或限制结果集，以满足您自己的业务或数据库脚本需求。

At this point, we need to determine what our stored procedure will do, and the most sensible order of operations. Ideally, we want to collect data as efficiently as possible, accessing system views only when needed and only collecting data for the parameters provided. In the spirit of optimization, we’ll refrain from using iteration as much as possible, instead opting for dynamic SQL, XML, or list generation, rather than WHILE loops or cursors.

此时，我们需要确定存储过程将执行的操作以及最明智的操作顺序。理想情况下，我们希望尽可能高效地收集数据，仅在需要时访问系统视图，并且仅收集所提供参数的数据。本着优化的精神，我们将尽可能避免使用迭代，而是选择动态SQL，XML或列表生成，而不是WHILE循环或游标。

Here is a basic outline of what we want to do, and the order we’ll want to accomplish it in:

这是我们要做什么的基本概述，以及我们要完成的顺序：

Validate data as we process parameter values and determine the work we need to do.
在处理参数值并确定需要做的工作时验证数据。
1. Schemas
  模式
2. Tables
  桌子
3. 1. Ordinal positions
    顺序位置
  2. Column length
    列长
  3. Column precision
    列精度
  4. Column scale
    列规模
  5. Collation
    校对
  6. Nullable?
    可空吗？
  7. 1. Identity seed
      身份种子
    2. Identity increment
      身份增量
  8. 1. Computed column definition
      计算列定义
  9. Sparse?
    疏？
  10. 1. Default constraint definition
      默认约束定义
4. Foreign keys
  外键
5. Check constraints
  检查约束
6. 1. Primary Keys
    主键
7. Triggers
  扳机
8. Extended properties
  扩展属性
Iterate through our table list, ensuring we generate the CREATE statements in a logical order.
遍历我们的表列表，确保我们按逻辑顺序生成CREATE语句。
Generate CREATE scripts using the metadata collected above.
使用上面收集的元数据生成CREATE脚本。
@Print_Results parameter. @Print_Results参数打印或选择结果。

构建模式文档解决方案 (Building the Schema Documentation Solution)

With a basic understanding of what we need to do, we should discuss the tools we will use before diving in. One important component of this script is dynamic SQL.

在基本了解我们需要做什么之后，我们应该讨论在使用之前要使用的工具。该脚本的重要组成部分是动态SQL。

动态SQL (Dynamic SQL)

In order to efficiently gather metadata from a database whose name is not known until runtime, we need to generate dynamic USE statements. The syntax will look like this each time:

为了有效地从名称直到运行时才知道的数据库中收集元数据，我们需要生成动态USE语句。每次的语法如下：

 
SELECT @Sql_Command = '
		USE [' + @Database_Name + ']';

@Sql_Command is an NVARCHAR(MAX) string that will be used to build, store and execute any dynamic SQL that we need to use. Any dynamic SQL stored in @Sql_Command that is executed in the same statement as the dynamic USE will be executed in the context of the database given by @Database_Name.

@Sql_Command是一个NVARCHAR（MAX）字符串，将用于构建，存储和执行我们需要使用的任何动态SQL。即在相同的语句作为动态使用执行存储在@Sql_Command任何动态SQL将通过@database_name给出的数据库的上下文中执行。

We’ll also need to filter our metadata queries based on the schema and table provided in the parameters. This will require dynamic WHERE clauses that will insert the parameters into those queries, ensuring we filter effectively. We alternatively could collect metadata on all tables and schemas and filter later, but this will execute faster and return less extraneous data. A dynamic WHERE clause searching for a specific schema would look like this:

我们还需要根据参数中提供的架构和表来过滤元数据查询。这将需要动态的WHERE子句，该子句会将参数插入这些查询中，以确保我们有效地进行过滤。或者，我们可以收集所有表和模式上的元数据并稍后进行过滤，但这将更快地执行并返回较少的无关数据。搜索特定模式的动态WHERE子句如下所示：

 
WHERE schemas.name = ''' + @Schema_Name + ''';'

When this WHERE clause is applied to a SELECT query, the results will filter on the schema provided by the @Schema_Name parameter, allowing us to focus exclusively on the tables that we care about.

当将此WHERE子句应用于SELECT查询时，结果将根据@Schema_Name参数提供的模式进行过滤，从而使我们可以专注于我们关注的表。

XML格式 (XML)

One aspect of foreign keys and indexes that we need to contend with are column lists. Indexes can contain any number of key columns and (optionally) include columns. A foreign key typically consists of a one-column-to-one-column relationship, but could be comprised of two, three, or more columns. We’d like to quickly generate a column list, and do so without the need for loops or multiple additional queries.

我们需要处理的外键和索引的一方面是列列表。索引可以包含任意数量的键列，并且（可选）包含列。外键通常由一列到一列的关系组成，但可以由两列，三列或更多列组成。我们希望快速生成一个列列表，而无需循环或多个其他查询。

A list can be generated using string manipulation, and can be done so very efficiently. The following example creates a comma separated list using table names from sys.tables:

列表可以使用字符串操作生成，并且可以非常有效地完成。以下示例使用sys.tables中的表名创建一个逗号分隔的列表：

 
DECLARE @string NVARCHAR(MAX) = '';
SELECT @string = @string + name + ','
FROM sys.tables
WHERE tables.is_ms_shipped = 0
ORDER BY tables.name;
SELECT @string = LEFT(@string, LEN(@string) - 1);
SELECT @string;

This syntax, in which we SELECT a string equal to itself plus additional table data allows that data to be compressed into the @string itself. While this syntax is extremely efficient, it cannot be combined easily with the SELECT of other columns. In order to get the best of both worlds and create a comma separated list while also gathering additional metadata, we’ll use XML instead.

这种语法（我们选择一个等于其自身的字符串以及其他表数据）允许将该数据压缩为@string本身。尽管此语法非常有效，但无法与其他列的SELECT轻松组合。为了获得两全其美的效果并创建一个用逗号分隔的列表，同时还收集其他元数据，我们将改用XML。

With XML, we can cram the same data as above into an XML object, and then use STUFF to put it into a string, delimited by commas, just as above. The syntax will look like this:

使用XML，我们可以将与上述相同的数据塞入XML对象，然后使用STUFF将其放入由逗号分隔的字符串中，就像上面一样。语法如下所示：

 
DECLARE @string NVARCHAR(MAX) = '';
SELECT @string = 
		STUFF(( SELECT ', ' + tables.name
					FROM sys.tables
					WHERE tables.is_ms_shipped = 0
					ORDER BY tables.name
				FOR XML PATH('')), 1, 2, '')
SELECT @string;

In both of the scenarios above, the output will be the expected CSV, which looks like this:

在上述两种情况下，输出均为预期的CSV，如下所示：

This strategy will allow us to collect index metadata, for example, while also compiling index column lists form within the same statement. While the TSQL isn’t as simple as if we collected each data element separately, it is more efficient and requires significantly less work to gather what we are looking for.

例如，这种策略将使我们能够收集索引元数据，同时还可以在同一条语句中编译索引列列表形式。尽管TSQL并不像我们单独收集每个数据元素那么简单，但它效率更高，并且所需的工作量大大减少了。

参数验证 (Parameter Validation)

If a stored procedure accepts parameters, it’s generally a good idea to validate those parameters and return a helpful error message if any problems are found. In the case of this search proc, we’ll be allowing the user to enter a database name, and optionally a schema and table name. As a result, data could be entered that is either invalid or does not match any database schema on our server. Let’s consider a handful of common scenarios:

如果存储过程接受参数，通常最好验证这些参数并在发现任何问题时返回有用的错误消息。在此搜索过程中，我们将允许用户输入数据库名称以及模式和表名称（可选）。结果，可能输入的数据无效或与我们服务器上的任何数据库模式都不匹配。让我们考虑一些常见的情况：

If no database name is provided or if it is NULL, we should bail immediately as this is required.
如果没有提供数据库名称或为NULL，则我们应立即保释，因为这是必需的。
If a schema is provided, but does not match any in the database provided, end processing and exit.
如果提供了一个架构，但与提供的数据库中的任何架构都不匹配，则结束处理并退出。
If a table is provided that matches none in the database, or none within the schema provided, also exit.
如果提供的表与数据库中的任何表都不匹配，或者在提供的模式中的任何表都不匹配，则也退出。

These checks ensure that we do not waste time attempting to process invalid objects. We can accomplish the database name check as follows:

这些检查确保我们不会浪费时间尝试处理无效的对象。我们可以完成数据库名称检查，如下所示：

 
IF @Database_Name IS NULL
	BEGIN
		RAISERROR('Document_Schema: Please provide a database name for this stored procedure', 16, 1);
		RETURN;			
	END

Once we’ve established that a database is provided, we can validate the schema provided. If NULL, then we will seach all schemas, but if it is provided, we can validate it like this:

一旦确定提供了数据库，就可以验证提供的架构。如果为NULL，那么我们将搜索所有模式，但是如果提供了它，我们可以像这样验证它：

 
SELECT @Sql_Command = '
			USE [' + @Database_Name + '];
			SELECT DISTINCT
				schemas.name
			FROM sys.schemas
			INNER JOIN sys.tables
			ON schemas.schema_id = tables.schema_id
			WHERE schemas.name = ''' + @Schema_Name + ''';'
		INSERT INTO @Schemas
			(Schema_Name)
		EXEC sp_executesql @Sql_Command;
 
		IF NOT EXISTS (SELECT * FROM @Schemas)
		BEGIN
			RAISERROR('Document_Schema: The schema name provided does not exist, or it contains no user tables', 16, 1);
			RETURN;			
		END

We INNER JOIN tables here in order to validate that the schema provided has tables associated with it. If not, then there’s no work to do and we can exit immediately. Once a schema with valid objects has been established, we can perform a similar check on table

我们在这里INNER JOIN表是为了验证提供的模式是否具有与其关联的表。如果没有，那么就没有工作可做，我们可以立即退出。建立具有有效对象的架构后，我们可以对表执行类似的检查

收集架构元数据 (Collect Schema Metadata)

This part will be fun and easy. Why? We did all of the work in the previous article! With the knowledge of what views we need to use, as well as what data to pull from them, the only remaining question is, how do we store and use this data throughout the stored procedure?

这部分将变得有趣而轻松。为什么？我们完成了上一篇文章中的所有工作！了解了我们需要使用哪些视图以及要从中获取哪些数据后，唯一剩下的问题是，我们如何在整个存储过程中存储和使用这些数据？

First off, we need to create some table variables that will hold all of the data that we collect. Since the data will be inserted and selected all at once, and because the volume of data will be relatively small, there’s no need to index these tables or worry too much about optimization. We’ll be careful to not collect any more data than is required for our work, and that in of itself will be the most significant way we can ensure that our stored proc will run efficiently.

首先，我们需要创建一些表变量，以保存我们收集的所有数据。由于将同时插入和选择数据，并且由于数据量相对较小，因此无需为这些表建立索引，也不必过多担心优化。我们将谨慎地收集不超过工作所需数据的数据，这本身将是确保存储的proc高效运行的最重要方法。

Here are some of the tables we’ll create and use to store our schema metadata, until we are ready to build create scripts later on:

这是我们将创建并用于存储架构元数据的一些表，直到我们准备稍后构建创建脚本为止：

 
DECLARE @Schemas TABLE
		(Schema_Name SYSNAME NOT NULL);
 
	DECLARE @Tables TABLE
		(Schema_Name SYSNAME, Table_Name SYSNAME NOT NULL, Result_Text NVARCHAR(MAX) NULL);
 
	DECLARE @Columns TABLE
		(Schema_Name SYSNAME NOT NULL, Table_Name SYSNAME NOT NULL, Column_Name SYSNAME NOT NULL, Type_Name SYSNAME NOT NULL, Ordinal_Position SMALLINT NOT NULL,
		 Column_Length SMALLINT NOT NULL, Column_Precision TINYINT NOT NULL, Column_Scale TINYINT NOT NULL, Column_Collation SYSNAME NULL, Is_Nullable BIT NOT NULL,
		 Is_Identity BIT NOT NULL, Is_Computed BIT NOT NULL, is_sparse BIT NOT NULL, Identity_Seed BIGINT NULL, Identity_Increment BIGINT NULL,
		 Default_Constraint_Name SYSNAME NULL, Default_Constraint_Definition NVARCHAR(MAX) NULL, Computed_Column_Definition NVARCHAR(MAX));
	
DECLARE @Foreign_Keys TABLE
		(Foreign_Key_Name SYSNAME NOT NULL, Foreign_Key_Schema_Name SYSNAME NOT NULL, Foreign_Key_Table_Name SYSNAME NOT NULL, Foreign_Key_Creation_Script NVARCHAR(MAX) NOT NULL);
 
	DECLARE @Check_Constraints TABLE
		(Schema_Name SYSNAME, Table_Name SYSNAME, Check_Constraint_Definition NVARCHAR(MAX));
 
	DECLARE @Indexes TABLE
		(Index_Name SYSNAME NOT NULL, Schema_Name SYSNAME NOT NULL, Table_Name SYSNAME NOT NULL, Is_Unique BIT NOT NULL, Has_Filter BIT NOT NULL, Filter_Definition NVARCHAR(MAX) NULL, Index_Type NVARCHAR(MAX) NOT NULL, Index_Column_List NVARCHAR(MAX) NULL, Include_Column_List NVARCHAR(MAX) NULL, Is_Primary_Key BIT NOT NULL);
 
	DECLARE @Triggers TABLE
		(Schema_Name SYSNAME NOT NULL, Table_Name SYSNAME NOT NULL, Trigger_Definition NVARCHAR(MAX) NOT NULL);
 
	DECLARE @Extended_Property TABLE
		(Object_Type NVARCHAR(60) NOT NULL, Extended_Property_Name SYSNAME NOT NULL, Extended_Property_Value NVARCHAR(MAX) NOT NULL, Schema_Name SYSNAME NOT NULL, Object_Name SYSNAME NOT NULL, Parent_Object_Name SYSNAME NULL, Parent_Column_Name SYSNAME NULL, Index_Name SYSNAME NULL);

That sure is a lot of tables, but my goal is to create a script that is efficient, easy to understand, and easy to modify. While we could get creative and think of ways to store everything in one huge table, being able to quickly modify or validate a single object type in a single table is far easier. I can’t think of any significant benefits of doing this that wouldn’t also introduce unwanted complexity or obfuscate our code more.

那肯定有很多表，但是我的目标是创建一个高效，易于理解和易于修改的脚本。尽管我们可以发挥创造力并考虑将所有内容存储在一个大表中的方法，但是能够快速修改或验证单个表中的单个对象类型要容易得多。我想不出这样做的任何重大好处，也不会带来不必要的复杂性或使我们的代码更加混乱。

Above, we have created tables for schemas, tables, columns, foreign keys, check constraints, indexes, triggers, and extended properties. Within each table, we gather as much data as we can in order to avoid having to go back for more later. The @Columns table in particular is quite hefty as it includes information about whether the column is computed, an identity, has a default constraint, if it is nullable, and more!

上面，我们为模式，表，列，外键，检查约束，索引，触发器和扩展属性创建了表。在每个表中，我们收集了尽可能多的数据，以避免以后再返回。 @Columns表特别庞大，因为它包含有关是否计算列，标识，具有默认约束（如果它可以为空）等信息！

The @Schemas and @Tables are much simpler and are intended for use as data validation tools to ensure that we correctly use parameter inputs throughout our stored proc.

@Schemas和@Tables更加简单，旨在用作数据验证工具，以确保我们在整个存储过程中正确使用参数输入。

As an additional convenience to our TSQL needs later on, we’ll generate a comma-separated list for the schema list, in the event that the user did not provide a specific schema to search. This will make any WHERE clause that checks the list of schemas easy to build:

为了以后满足TSQL的需求，如果用户未提供要搜索的特定模式，我们将为模式列表生成一个以逗号分隔的列表。这将使检查架构列表的任何WHERE子句都易于构建：

 
DECLARE @Schema_List NVARCHAR(MAX) = '';
	SELECT
		@Schema_List = @Schema_List + '''' + Schema_Name + ''','
	FROM @Schemas;
	SELECT @Schema_List = LEFT(@Schema_List, LEN(@Schema_List) - 1); -- Remove trailing comma.

The result is a string that can be used whenever a list of schemas is required.

结果是一个字符串，可在需要模式列表时使用。

When executing dynamic SQL on another database, we have several options available in order to capture the data collected within the query and get it into one of these tables. I’ll opt for using the INSERT INTO…EXEC sp_executesql syntax, which will insert the results of the dynamic SQL statement directly into the table provided. Here is an example of what this looks like:

在另一个数据库上执行动态SQL时，我们有几个可用选项，以捕获查询中收集的数据并将其放入这些表之一。我将选择使用INSERT INTO…EXEC sp_executesql语法，该语法会将动态SQL语句的结果直接插入提供的表中。这是一个看起来像这样的例子：

 
SELECT @Sql_Command = '
		USE [' + @Database_Name + '];
		SELECT DISTINCT
			schemas.name AS Schema_Name,
			tables.name AS Table_Name
		FROM sys.tables
		INNER JOIN sys.schemas
		ON schemas.schema_id = tables.schema_id
		WHERE tables.is_ms_shipped = 0';
	IF @Table_Name IS NOT NULL
	BEGIN
		SELECT @Sql_Command = @Sql_Command + '
		AND tables.name = ''' + @Table_Name + '''';
	END
	SELECT @Sql_Command = @Sql_Command + '
		AND schemas.name IN (' + @Schema_List + ')';
 
	INSERT INTO @Tables
		(Schema_Name, Table_Name)
	EXEC sp_executesql @Sql_Command;

In this dynamic SQL query, we:

在此动态SQL查询中，我们：

Change database context to the database provided by user input.
将数据库上下文更改为用户输入提供的数据库。
Select table and schema data, filtering out any system tables.
选择表和架构数据，过滤掉所有系统表。
If a table name was provided via user input, add that optional filter in.
如果通过用户输入提供了表名，请添加该可选过滤器。
Add a filter for the schema list.
为架构列表添加过滤器。
Execute the dynamic SQL statement and insert the results directly into @Tables.
执行动态SQL语句并将结果直接插入@Tables。

This syntax is convenient when we only have a single SELECT statement to be concerned with in our dynamic SQL. If there were multiple SELECTS, or scalar variables to update, we would want to consider parameterizing sp_executesql, using temporary tables, or both. For our purposes, though, directly inserting to table variables works great and fits our needs perfectly!

当我们在动态SQL中只需要关注一个SELECT语句时，此语法很方便。如果有多个SELECTS或标量变量要更新，我们将要考虑使用临时表或同时使用sp_executesql参数化。但是，出于我们的目的，直接插入表变量的效果很好，非常适合我们的需求！

Much of our documentation script will follow this exact method, doing so for all of the tables we reviewed above. Some queries will be more complex, as they will need to retrieve column lists, or other more involved data from system views, but the overall process is same for each. Reviewing our work from the previous article will help explain why each of these queries is written as they are, before they are inserted into dynamic SQL statements.

我们的许多文档脚本都将遵循此确切方法，对于我们上面检查过的所有表都采用这种方法。有些查询会更复杂，因为它们将需要从系统视图中检索列列表或其他涉及更多的数据，但是每个查询的总体过程是相同的。回顾前一篇文章中的工作，将有助于解释为什么在将这些查询插入动态SQL语句之前按原样编写这些查询。

构建模式创建语句 (Build Schema Create Statements)

Once we have collected all of the schema metadata that we are interested, it’s time for the big task: Turning that information into valid CREATE statements. This is wholly an exercise in both string manipulation and patience, but one that can be easily tested and validated along the way. If our output TSQL is displaying the wrong metadata, is out of order, or is missing something, figuring out why isn’t too tough as we saved ALL of our data in table variables. Each of these can be reviewed anytime in order to validate correctness and completeness of the data within.

一旦我们收集了所有感兴趣的模式元数据，就该完成大任务了：将这些信息转换为有效的CREATE语句。这完全是在字符串操作和耐心方面的一种练习，但是很容易在此过程中进行测试和验证。如果我们的输出TSQL显示错误的元数据，乱序或丢失某些内容，请弄清楚为什么不那么困难，因为我们将所有数据保存在表变量中。可以随时检查其中的每一个，以验证其中数据的正确性和完整性。

In order to facilitate the documentation, in order, of each table, we will use a cursor to iterate through each one-by-one. While it is possible to generate create statements in order with a set-based approach, this alternative would be significantly more complex and remove much of the desired flexibility from this solution. Let’s review how we’d generate scripts for a single table within this loop. Keep in mind that we will be saving the creation scripts in the table variable @Tables, which will provide some flexibility later on in terms of how we output our scripts.

为了方便每个表的文档编制，我们将使用游标逐个迭代。尽管可以使用基于集合的方法按顺序生成create语句，但是此替代方案将更加复杂，并且会从此解决方案中消除很多期望的灵活性。让我们回顾一下如何在此循环内为单个表生成脚本。请记住，我们将创建脚本保存在表变量@Tables中 ，这将在以后输出脚本的方式方面提供一些灵活性。

 
DECLARE @Schema_Build_Text NVARCHAR(MAX);

This string will be used to build and store our script as it is put together. Each table may have many objects, and will therefore need multiple steps in order to complete construction of its script. As we iterate though tables, we’ll keep track of the current schema and table using the following two strings:

该字符串将被用于构建和存储放在一起的脚本。每个表可能有许多对象，因此需要多个步骤才能完成其脚本的构造。遍历表时，我们将使用以下两个字符串跟踪当前的模式和表：

 
DECLARE @Schema_Name_Current NVARCHAR(MAX);
DECLARE @Table_Name_Current NVARCHAR(MAX);

Each creation script will filter on these variables to ensure we are only compiling metadata for the current object (table/schema).

每个创建脚本都会对这些变量进行过滤，以确保我们只为当前对象（表/架构）编译元数据。

桌子 (Tables)

The start of each table creation script is as simple as CREATE TABLE:

每个表创建脚本的开始都像CREATE TABLE一样简单：

 
SELECT @Schema_Build_Text = 'USE [' + @Database_Name + '];' + '
 
' +
		'CREATE TABLE [' + TABLE_DATA.Schema_Name + '].[' + TABLE_DATA.Table_Name + ']('
		FROM @Tables TABLE_DATA
		WHERE TABLE_DATA.Schema_Name = @Schema_Name_Current
		AND TABLE_DATA.Table_Name = @Table_Name_Current

This first section sets the database context to whatever database we are analyzing and then puts together the CREATE TABLE statement, filtered on the current table and schema we are working on. Square brackets are used around all object names, which ensures that we are able to manage any that have spaces in their names.

第一部分将数据库上下文设置为我们正在分析的任何数据库，然后将CREATE TABLE语句放在一起，并根据我们正在处理的当前表和模式进行过滤。方括号用于所有对象名称，以确保我们能够管理名称中带有空格的任何对象。

列 (Columns)

Next, we need to list out the columns in this table, in the correct order, with any relevant attributes defined inline with the table creation. This is a bit more involved as we may have any number of columns in a table and any number of attributes in any combination. Our TSQL needs to be robust enough to deal with any combinations that our schema may throw at us. We’ll break our workflow down as follows:

接下来，我们需要以正确的顺序列出该表中的列，并在表创建时内联定义任何相关属性。这涉及更多，因为我们表中可以有任意数量的列，并且可以任意组合任意数量的属性。我们的TSQL必须足够健壮，以处理我们的架构可能抛出的任何组合。我们将按以下方式细分工作流程：

Output the column name.
输出列名。
Is the column computed? If so, skip other attributes and go straight to its definition.
会计算列吗？如果是这样，请跳过其他属性并直接转到其定义。
1. DECIMAL? If so, then include precision and scale.
  十进制？如果是这样，则包括精度和比例。
2. VARCHAR/NVARCHAR/CHAR/NCHAR? If so, then include the column length.
  VARCHAR / NVARCHAR / CHAR / NCHAR？如果是这样，则包括列长。
If the column is sparse, include that attribute here.
如果列稀疏，请在此处添加该属性。
Is the column an identity? If so, add that attribute, including the identity seed and increment.
列是身份吗？如果是这样，请添加该属性，包括身份种子和增量。
Is the column nullable? If so, specify NULL or NOT NULL here. While SQL Server defaults columns to NULL when unspecified, we will be thorough and always include one or the other here.
该列可以为空吗？如果是这样，请在此处指定NULL或NOT NULL。尽管未指定时，SQL Server默认将列设置为NULL，但我们会很详尽，并且始终在此处包括一个或另一个。
Is there a default constraint on this column? If so, include the constraint name and definition here.
此列是否有默认约束？如果是这样，请在此处包括约束名称和定义。
Is the column computed? If so, this is where the definition will go.
会计算列吗？如果是这样，这就是定义的地方。

After all of these steps, we will have a column list, including important attributes. The TSQL to generate this script segment is as follows:

完成所有这些步骤之后，我们将获得一个包含重要属性的列列表。生成此脚本段的TSQL如下：

 
SELECT
			@Schema_Build_Text = @Schema_Build_Text + '
	' + COLUMN_DATA.Column_Name + ' ' + 
			CASE WHEN COLUMN_DATA.Is_Computed = 1 THEN '' ELSE -- Don't add metadata if a column is computed (just include definition)
			COLUMN_DATA.Type_Name + -- Basic column metadata
			CASE WHEN COLUMN_DATA.Type_Name = 'DECIMAL' THEN '(' + CAST(COLUMN_DATA.Column_Precision AS NVARCHAR(MAX)) + ',' + CAST(COLUMN_DATA.Column_Scale AS NVARCHAR(MAX)) + ')' ELSE '' END + -- Column precision (decimal)
			CASE WHEN COLUMN_DATA.Type_Name IN ('VARCHAR', 'NVARCHAR', 'NCHAR', 'CHAR') THEN '(' + CAST(COLUMN_DATA.Column_Length AS NVARCHAR(MAX)) + ')' ELSE '' END + -- Column length (string)
			CASE WHEN COLUMN_DATA.is_sparse = 1 THEN ' SPARSE' ELSE '' END + -- If a column is sparse, denote that here.
			CASE WHEN COLUMN_DATA.Is_Identity = 1 THEN ' IDENTITY(' + CAST(Identity_Seed AS NVARCHAR(MAX)) + ',' + CAST(Identity_Increment AS NVARCHAR(MAX)) + ')' ELSE '' END + -- Identity Metadata (optional)
			CASE WHEN COLUMN_DATA.Is_Nullable = 1 THEN ' NULL' ELSE ' NOT NULL' END + -- NULL/NOT NULL definition
			CASE WHEN COLUMN_DATA.Default_Constraint_Name IS NOT NULL THEN ' CONSTRAINT ' + COLUMN_DATA.Default_Constraint_Name + ' DEFAULT ' + COLUMN_DATA.Default_Constraint_Definition ELSE '' END END + -- Default constraint definition (optional)
			CASE WHEN COLUMN_DATA.Is_Computed = 1 THEN 'AS ' + COLUMN_DATA.Computed_Column_Definition ELSE '' END + ','
		FROM @Columns COLUMN_DATA
		WHERE COLUMN_DATA.Table_Name = @Table_Name_Current
		AND COLUMN_DATA.Schema_Name = @Schema_Name_Current
		ORDER BY COLUMN_DATA.Ordinal_Position ASC;
 
		SELECT @Schema_Build_Text = LEFT(@Schema_Build_Text, LEN(@Schema_Build_Text) - 1);

The filter ensures we only add columns for the current table we are working on, while the ORDER BY puts our data in the correct column order, based on column ordinals. The list-building syntax used here allows us to build the column list in a single statement from a set of columns of any size. The final SELECT removes the trailing comma from the string, which is left over by the list-building syntax.

过滤器可确保我们仅为正在处理的当前表添加列，而ORDER BY则根据列序将数据按正确的列顺序放置。此处使用的列表构建语法使我们能够在单个语句中从任意大小的一组列中构建列列表。最终的SELECT从字符串中删除结尾的逗号，该字符串由列表构建语法保留。

主键 (Primary Keys)

If the table has a primary key, then we can include that definition defined inline at the end of the table creation. Since there can only be a single primary key per table, there’s no need to worry about lists of data. We can generate the script for its creation in a single step as follows:

如果表具有主键，那么我们可以在表创建结束时内联包含该定义。由于每个表只能有一个主键，因此无需担心数据列表。我们可以在一个步骤中生成用于创建脚本的脚本，如下所示：

 
IF EXISTS (SELECT * FROM @Indexes PRIMARY_KEY_DATA WHERE PRIMARY_KEY_DATA.Schema_Name = @Schema_Name_Current AND PRIMARY_KEY_DATA.Table_Name = @Table_Name_Current AND PRIMARY_KEY_DATA.Is_Primary_Key = 1)
		BEGIN
			SELECT
				@Schema_Build_Text = @Schema_Build_Text + ',
CONSTRAINT ' + PRIMARY_KEY_DATA.Index_Name + ' PRIMARY KEY' + -- Primary key name
				Index_Type + -- Clustered vs. Nonclustered key.
				'(' + PRIMARY_KEY_DATA.Index_Column_List + ')' -- Column list.
			FROM @Indexes PRIMARY_KEY_DATA
			WHERE PRIMARY_KEY_DATA.Schema_Name = @Schema_Name_Current
			AND PRIMARY_KEY_DATA.Table_Name = @Table_Name_Current
			AND PRIMARY_KEY_DATA.Is_Primary_Key = 1;
		END

The IF EXISTS checks if a primary key exists, and if not, this section will be skipped. Otherwise, we build the primary key creation TSQL using the index information collected earlier, verifying that Is_Primary_Key = 1. Since we built the index column list earlier, we need only return them now, without any need for further list building.

IF EXISTS检查是否存在主键，如果不存在，则将跳过此部分。否则，我们将使用先前收集的索引信息来构建主键创建TSQL，并验证Is_Primary_Key =1。由于我们先前构建了索引列列表，因此我们现在只需要返回它们，而无需进行进一步的列表构建。

Now that we have completed the table creation statement, we can close the parenthesis from earlier and add a “GO” in order to start a new batch:

现在我们已经完成了表创建语句，可以从前面关闭括号并添加“ GO”以开始新的批处理：

 
SELECT @Schema_Build_Text = @Schema_Build_Text + ');
GO';

外键 (Foreign Keys)

The remainder of the CREATE/ALTER statements can be listed in any order. The order chosen here was based on which parts of this script were coded first. Feel free to adjust order if it helps in the consumption of this scripted metadata. In the case of foreign keys, we generated ALTER TABLE statements previously, which saved time when this moment came along. Here is the collection TSQL used to initially collect foreign key information:

其余CREATE / ALTER语句可以按任何顺序列出。此处选择的顺序基于该脚本的哪些部分首先被编码。如果可以帮助使用此脚本化的元数据，请随时调整顺序。对于外键，我们之前生成了ALTER TABLE语句，这节省了时间。这是用于最初收集外键信息的TSQL集合：

 
SELECT @Sql_Command = '
	USE [' + @Database_Name + '];
 
	SELECT 
		FOREIGN_KEY_DATA.name AS Foreign_Key_Name,
		FOREIGN_KEY_SCHEMA.name AS Foreign_Key_Schema_Name,
		FOREIGN_KEY_TABLE.name AS Foreign_Key_Table_Name,
		''ALTER TABLE ['' + FOREIGN_KEY_SCHEMA.name + ''].['' + FOREIGN_KEY_TABLE.name + ''] WITH '' + CASE WHEN FOREIGN_KEY_DATA.is_not_trusted = 1 THEN ''NOCHECK'' ELSE ''CHECK'' END +
							   '' ADD CONSTRAINT ['' + FOREIGN_KEY_DATA.name + '']
FOREIGN KEY('' + STUFF(( SELECT '', '' + FOREIGN_KEY_COLUMN.name
			FROM sys.foreign_keys
			INNER JOIN sys.foreign_key_columns
			ON foreign_keys.object_id = foreign_key_columns.constraint_object_id
			INNER JOIN sys.tables FOREIGN_KEY_TABLE
			ON foreign_keys.parent_object_id = FOREIGN_KEY_TABLE.object_id
			INNER JOIN sys.schemas FOREIGN_KEY_SCHEMA
			ON FOREIGN_KEY_SCHEMA.schema_id = FOREIGN_KEY_TABLE.schema_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
			WHERE FOREIGN_KEY_DATA.object_id = foreign_keys.object_id
			AND FOREIGN_KEY_DATA.name = foreign_keys.name
			ORDER BY FOREIGN_KEY_TABLE.name, foreign_key_columns.constraint_column_id
			FOR XML PATH('''')), 1, 2, '''') + '')
REFERENCES ['' + FOREIGN_KEY_SCHEMA.name + ''].['' + REFERENCED_TABLE.name + ''] ('' + 
		STUFF(( SELECT '', '' + REFERENECD_COLUMN.name
					FROM sys.foreign_keys
					INNER JOIN sys.foreign_key_columns
					ON foreign_keys.object_id = foreign_key_columns.constraint_object_id
					INNER JOIN sys.tables REFERENCED_TABLE
					ON foreign_keys.referenced_object_id = REFERENCED_TABLE.object_id
					INNER JOIN sys.schemas REFERENCED_KEY_SCHEMA
					ON REFERENCED_KEY_SCHEMA.schema_id = REFERENCED_TABLE.schema_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
					WHERE FOREIGN_KEY_DATA.object_id = foreign_keys.object_id
					AND FOREIGN_KEY_DATA.name = foreign_keys.name
					ORDER BY REFERENCED_TABLE.name, foreign_key_columns.constraint_column_id
				FOR XML PATH('''')), 1, 2, '''') + ''));
GO''
		AS Foreign_Key_Creation_Script
	FROM sys.foreign_keys FOREIGN_KEY_DATA
	INNER JOIN sys.tables FOREIGN_KEY_TABLE
	ON FOREIGN_KEY_DATA.parent_object_id = FOREIGN_KEY_TABLE.object_id
	INNER JOIN sys.tables REFERENCED_TABLE
	ON FOREIGN_KEY_DATA.referenced_object_id = REFERENCED_TABLE.object_id
	INNER JOIN sys.schemas FOREIGN_KEY_SCHEMA
	ON FOREIGN_KEY_SCHEMA.schema_id = FOREIGN_KEY_TABLE.schema_id
	INNER JOIN sys.schemas REFERENCED_KEY_SCHEMA
	ON REFERENCED_KEY_SCHEMA.schema_id = REFERENCED_TABLE.schema_id';
	INSERT INTO @Foreign_Keys
		(Foreign_Key_Name, Foreign_Key_Schema_Name, Foreign_Key_Table_Name, Foreign_Key_Creation_Script)
	EXEC sp_executesql @Sql_Command;

List-building via XML was used in order to gather the foreign key column lists, as well as the referenced column lists all in a single statement. The syntax here is almost identical to that used for the collection of index key and included column lists. Since this work is already complete, the steps needed to generate the foreign key creation scripts will be much simpler:

使用通过XML进行列表构建是为了在单个语句中收集外键列列表以及所有引用的列列表。此处的语法几乎与用于收集索引键和包含的列列表的语法相同。由于这项工作已经完成，因此生成外键创建脚本所需的步骤将更加简单：

 
IF EXISTS (SELECT * FROM @Foreign_Keys WHERE Foreign_Key_Schema_Name = @Schema_Name_Current AND Foreign_Key_Table_Name = @Table_Name_Current)
		BEGIN
			SELECT
				@Schema_Build_Text = @Schema_Build_Text + '
 
' + Foreign_Key_Creation_Script
			FROM @Foreign_Keys
			WHERE Foreign_Key_Schema_Name = @Schema_Name_Current
			AND Foreign_Key_Table_Name = @Table_Name_Current;
		END

IF EXISTS checks to see if any foreign keys exist on the table, and if so, add the foreign key creation script onto our schema creation script.

IF EXISTS检查表上是否存在任何外键，如果存在，则将外键创建脚本添加到我们的模式创建脚本中。

检查约束 (Check Constraints )

Check constraints creation statements were also created previously using the following TSQL:

检查约束创建语句也是以前使用以下TSQL创建的：

 
SELECT @Sql_Command = '
		USE [' + @Database_Name + '];
		SELECT
			schemas.name AS Schema_Name,
			tables.name AS Table_Name,
			''ALTER TABLE ['' + schemas.name + ''].['' + tables.name + '']
WITH '' + CASE WHEN check_constraints.is_not_trusted = 1 THEN ''NOCHECK'' ELSE ''CHECK'' END + '' ADD CONSTRAINT ['' + check_constraints.name + '']
CHECK '' + check_constraints.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';
		IF @Schema_Name IS NOT NULL
		BEGIN
			SELECT @Sql_Command = @Sql_Command + '
			WHERE schemas.name = ''' + @Schema_Name + '''';
		END
		IF @Table_Name IS NOT NULL AND @Schema_Name IS NOT NULL
		BEGIN
			SELECT @Sql_Command = @Sql_Command + '
			AND tables.name = ''' + @Table_Name + '''';
		END
		IF @Table_Name IS NOT NULL AND @Schema_Name IS NULL
		BEGIN
			SELECT @Sql_Command = @Sql_Command + '
			WHERE tables.name = ''' + @Table_Name + '''';
		END
 
	INSERT INTO @Check_Constraints
		(Schema_Name, Table_Name, Check_Constraint_Definition)
	EXEC sp_executesql @Sql_Command;

While no column lists were needed for this work, we did need to verify if a constraint was created with the NOCHOCK attribute. Otherwise, the creation statement is relatively simple. Optional filters on schema and table help reduce the data returned to include only what we are interested in based on the stored proc parameters. Once we have the creation script, we can script out the check constraints as follows:

尽管这项工作不需要任何列列表，但是我们确实需要验证是否使用NOCHOCK属性创建了约束。否则，创建语句相对简单。模式和表上的可选过滤器有助于减少返回的数据，使其仅包含基于存储的proc参数我们感兴趣的数据。一旦有了创建脚本，就可以编写检查约束脚本，如下所示：

 
IF EXISTS (SELECT * FROM @Check_Constraints WHERE Schema_Name = @Schema_Name_Current AND Table_Name = @Table_Name_Current)
		BEGIN
			SELECT
				@Schema_Build_Text = @Schema_Build_Text + '
 
' + Check_Constraint_Definition + '
GO'
			FROM @Check_Constraints
			WHERE Schema_Name = @Schema_Name_Current
			AND Table_Name = @Table_Name_Current;
		END

If any check constraints exist, then add the previously-created scripts to our growing table creation script.

如果存在任何检查约束，则将先前创建的脚本添加到我们不断增长的表创建脚本中。

指标 (Indexes)

Indexes have a number of attributes on them that need to be integrated into our TSQL here. We’ll need to check if an index is unique, XML, clustered, filtered, or has include columns:

索引具有许多属性，这些属性需要在此处集成到我们的TSQL中。我们将需要检查索引是唯一的，XML的，聚簇的，已过滤的还是具有包含列的：

 
IF EXISTS (SELECT * FROM @Indexes WHERE Schema_Name = @Schema_Name_Current AND Table_Name = @Table_Name_Current)
		BEGIN
			SELECT
				@Schema_Build_Text = @Schema_Build_Text + '
 
CREATE ' + CASE WHEN Is_Unique = 1 THEN 'UNIQUE ' ELSE '' END + CASE WHEN Index_Type = 'XML' THEN 'PRIMARY ' ELSE '' END + Index_Type + ' INDEX [' + Index_Name + '] ON [' + Schema_Name + '].[' + Table_Name + ']
(' + Index_Column_List + ')' + CASE WHEN Include_Column_List <> '' THEN '
INCLUDE (' + Include_Column_List + ')' ELSE '' END +
CASE WHEN Has_Filter = 1 THEN '
WHERE ' + Filter_Definition ELSE '' END + '
GO'
			FROM @Indexes
			WHERE Schema_Name = @Schema_Name_Current
			AND Table_Name = @Table_Name_Current
			AND Is_Primary_Key = 0;
		END

We verify that indexes exist before proceeding, and ensure in the filter that we omit the primary key, since it has already been included in our script. The hefty use of CASE statements adds some complexity to this script, but allows our script to be much shorter than if we had handled each attribute in a separate string manipulation segment.

我们在继续操作之前先验证索引是否存在，并确保在过滤器中省略主键，因为主键已包含在主键中。 CASE语句的大量使用为该脚本增加了一些复杂性，但与我们在单独的字符串操作段中处理每个属性的脚本相比，该脚本要短得多。

扳机 (Triggers)

Trigger definitions are included in their entirety within the sys.triggers/sys.sql_modules relationship. As a result, adding trigger information to our script is quite simple:

触发器定义完整包含在sys.triggers / sys.sql_modules关系中。结果，将触发信息添加到我们的脚本非常简单：

 
 IF EXISTS (SELECT * FROM @Triggers WHERE Schema_Name = @Schema_Name_Current AND Table_Name = @Table_Name_Current)
		BEGIN
			SELECT
				@Schema_Build_Text = @Schema_Build_Text + '
' + Trigger_Definition + 'GO'
			FROM @Triggers
			WHERE Schema_Name = @Schema_Name_Current
			AND Table_Name = @Table_Name_Current
		END

If triggers exist, we need only add their text to our string and we’re done!

如果存在触发器，我们只需要将其文本添加到字符串中就可以了！

扩展属性 (Extended Properties)

This is a bit of a quirky addition, but worth including in our script as it demonstrates some more unusual metadata within SQL Server. Extended properties can be assigned to many types of objects, such as columns, tables, foreign keys, triggers, or more!

这有点古怪，但值得在我们的脚本中包括它，因为它演示了SQL Server中一些更不寻常的元数据。可以将扩展属性分配给许多类型的对象，例如列，表，外键，触发器或更多对象！

Our TSQL will need to verify what kind of object we are dealing with and script out the generation of the extended property using the correct syntax. Extended properties are created using the sp_addextendedproperty system stored procedure. The parameters specify the property name, the value that is assigned to it, and then 3 additional parameters to specify the type of object being tagged:

我们的TSQL将需要验证我们正在处理的对象类型，并使用正确的语法来编写扩展属性的生成。扩展属性是使用sp_addextendedproperty系统存储过程创建的。这些参数指定属性名称，为其分配的值，然后使用3个其他参数指定要标记的对象的类型：

 
type of object being tagged:
		IF EXISTS (SELECT * FROM @Extended_Property WHERE COALESCE(Parent_Object_Name, Object_Name, '') = @Table_Name_Current AND Schema_Name = @Schema_Name_Current)
		BEGIN
			SELECT
				@Schema_Build_Text = @Schema_Build_Text +
				'
 
EXEC sys.sp_addextendedproperty @name = ''' + Extended_Property_Name + ''', @value = ''' + Extended_Property_Value + ''', @level0type = ''SCHEMA'', @level0name = ''' +
				Schema_Name + ''', @level1type = ''TABLE'', @level1name = ''' + COALESCE(Parent_Object_Name, Object_Name, '') + '''' + CASE WHEN NOT(Object_Type = 'USER_TABLE' AND Parent_Object_Name IS NULL AND Parent_Column_Name IS NULL AND Index_Name IS NULL) THEN
				', @level2type = ''' + CASE WHEN Parent_Column_Name IS NOT NULL THEN 'COLUMN'
											WHEN Index_Name IS NOT NULL THEN 'INDEX'
											WHEN Object_Type LIKE '%CONSTRAINT%' THEN 'CONSTRAINT'
											WHEN Object_Type LIKE '%TRIGGER%' THEN 'TRIGGER'
									   END +
				''', @level2name = ''' + COALESCE(Parent_Column_Name, Index_Name, Object_Name) + ''''
				ELSE '' END + ';
GO'
			FROM @Extended_Property
			WHERE COALESCE(Parent_Object_Name, Object_Name, '') = @Table_Name_Current
			AND Schema_Name = @Schema_Name_Current;
		END

While that isn’t the prettiest TSQL ever written, it uses a small number of variables in order to generate valid extended property creation statements. We’ll get a chance to see what the results look like in our demo below.

虽然这不是有史以来最漂亮的TSQL，但它使用少量变量来生成有效的扩展属性创建语句。在下面的演示中，我们将有机会看到结果。

最后步骤 (Final Steps)

With all attributes added to @Schema_Build_Text, we can finally store this information in @Tables and then, when we exit the loop, select or print out the results. The following TSQL stores our results after each iteration:

将所有属性添加到@Schema_Build_Text之后 ，我们最终可以将该信息存储在@Tables中 ，然后在退出循环时选择或打印结果。以下TSQL在每次迭代后存储我们的结果：

 
 UPDATE TABLE_DATA
			SET Result_Text = @Schema_Build_Text
		FROM @Tables TABLE_DATA
		WHERE TABLE_DATA.Schema_Name = @Schema_Name_Current
		AND TABLE_DATA.Table_Name = @Table_Name_Current;

With all of our metadata collected, we can now return our results. If @Print_Results = 0, then the @Tables table will be selected in its entirety. If @Print_Results = 0, then we will iterate through tables, printing output one section at a time. The following TSQL will accomplish this task:

收集了所有元数据后，我们现在可以返回结果。如果@Print_Results = 0，则将完整选择@Tables表。如果@Print_Results = 0，那么我们将遍历表，一次打印输出一节。以下TSQL将完成此任务：

 
IF @Print_Results = 0
	BEGIN
		SELECT
			*
		FROM @Tables;
	END
	ELSE
	BEGIN
		DECLARE table_cursor CURSOR FOR SELECT Result_Text FROM @Tables;
		DECLARE @Result_Text NVARCHAR(MAX);
		OPEN table_cursor;
		FETCH NEXT FROM table_cursor INTO @Result_Text;
		WHILE @@FETCH_STATUS = 0
		BEGIN
			PRINT @Result_Text
			FETCH NEXT FROM table_cursor INTO @Result_Text;
		END
		CLOSE table_cursor;
		DEALLOCATE table_cursor;
	END

In general, printing the results is great for testing or small result sets, but may be problematic for large volumes of information. Selecting the data will be more reliable, allows for that data to be stored somewhere permanent, and avoids character limits in SQL Server Management Studio.

通常，打印结果非常适合测试或较小的结果集，但对于大量信息可能会出现问题。选择数据将更加可靠，允许将数据永久存储在某个地方，并且避免了SQL Server Management Studio中的字符限制。

最终结果 (The Final Results)

Well, we’ve built this big script that can generate schema creation statements. Let’s take it for a whirl! The following will execute this stored procedure on Person.Person, printing results, and including extended properties:

好吧，我们已经构建了可以生成架构创建语句的大型脚本。让我们旋转一下！以下将在Person.Person上执行此存储过程，打印结果并包括扩展属性：

 
EXEC dbo.Document_Schema
@Database_Name = 'AdventureWorks2014',
@Schema_Name = 'Person',
@Table_Name = 'Person',
@Print_Results = 1,
	@Show_Extended_Properties = 1;

A segment of the results are as follows:

结果的一部分如下：

 
USE [AdventureWorks2014];
 
CREATE TABLE [Person].[Person](
	BusinessEntityID INT NOT NULL,
	PersonType NCHAR(4) NOT NULL,
	NameStyle NAMESTYLE NOT NULL CONSTRAINT DF_Person_NameStyle DEFAULT ((0)),
	Title NVARCHAR(16) NULL,
	FirstName NAME NOT NULL,
	MiddleName NAME NULL,
	LastName NAME NOT NULL,
	Suffix NVARCHAR(20) NULL,
	EmailPromotion INT NOT NULL CONSTRAINT DF_Person_EmailPromotion DEFAULT ((0)),
	AdditionalContactInfo XML NULL,
	Demographics XML NULL,
	rowguid UNIQUEIDENTIFIER NOT NULL CONSTRAINT DF_Person_rowguid DEFAULT (NEWID()),
	ModifiedDate DATETIME NOT NULL CONSTRAINT DF_Person_ModifiedDate DEFAULT (GETDATE()),
CONSTRAINT PK_Person_BusinessEntityID PRIMARY KEYCLUSTERED(BusinessEntityID ASC));
GO
 
ALTER TABLE [Person].[Person] WITH CHECK ADD CONSTRAINT [FK_Person_BusinessEntity_BusinessEntityID]
FOREIGN KEY(BusinessEntityID)
REFERENCES [Person].[BusinessEntity] (BusinessEntityID));
GO
 
ALTER TABLE [Person].[Person]
WITH CHECK ADD CONSTRAINT [CK_Person_EmailPromotion]
CHECK ([EmailPromotion]>=(0) AND [EmailPromotion]<=(2))
GO
 
ALTER TABLE [Person].[Person]
WITH CHECK ADD CONSTRAINT [CK_Person_PersonType]
CHECK ([PersonType] IS NULL OR (upper([PersonType])='GC' OR upper([PersonType])='SP' OR upper([PersonType])='EM' OR upper([PersonType])='IN' OR upper([PersonType])='VC' OR upper([PersonType])='SC'))
GO
 
CREATE NONCLUSTERED INDEX [IX_Person_LastName_FirstName_MiddleName] ON [Person].[Person]
(LastName ASC, FirstName ASC, MiddleName ASC)
GO
 
CREATE UNIQUE NONCLUSTERED INDEX [AK_Person_rowguid] ON [Person].[Person]
(rowguid ASC)
GO
 
CREATE PRIMARY XML INDEX [PXML_Person_AddContact] ON [Person].[Person]
(AdditionalContactInfo)
GO
 
CREATE PRIMARY XML INDEX [PXML_Person_Demographics] ON [Person].[Person]
(Demographics)
GO
 
CREATE PRIMARY XML INDEX [XMLPATH_Person_Demographics] ON [Person].[Person]
(Demographics)
GO
 
CREATE PRIMARY XML INDEX [XMLPROPERTY_Person_Demographics] ON [Person].[Person]
(Demographics)
GO
 
CREATE PRIMARY XML INDEX [XMLVALUE_Person_Demographics] ON [Person].[Person]
(Demographics)
GO
 
CREATE TRIGGER [Person].[iuPerson] ON [Person].[Person] 
AFTER INSERT, UPDATE NOT FOR REPLICATION AS 
BEGIN
    DECLARE @Count int;
 
    SET @Count = @@ROWCOUNT;
    IF @Count = 0 
        RETURN;
 
    SET NOCOUNT ON;
 
    IF UPDATE([BusinessEntityID]) OR UPDATE([Demographics]) 
    BEGIN
        UPDATE [Person].[Person] 
        SET [Person].[Person].[Demographics] = N'<IndividualSurvey xmlns="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey"> 
            <TotalPurchaseYTD>0.00</TotalPurchaseYTD> 
            </IndividualSurvey>' 
        FROM inserted 
        WHERE [Person].[Person].[BusinessEntityID] = inserted.[BusinessEntityID] 
            AND inserted.[Demographics] IS NULL;
        
        UPDATE [Person].[Person] 
        SET [Demographics].modify(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey"; 
            insert <TotalPurchaseYTD>0.00</TotalPurchaseYTD> 
            as first 
            into (/IndividualSurvey)[1]') 
        FROM inserted 
        WHERE [Person].[Person].[BusinessEntityID] = inserted.[BusinessEntityID] 
            AND inserted.[Demographics] IS NOT NULL 
            AND inserted.[Demographics].exist(N'declare default element namespace 
                "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey"; 
                /IndividualSurvey/TotalPurchaseYTD') <> 1;
    END;
END;
GO
 
EXEC sys.sp_addextendedproperty @name = 'MS_Description', @value = 'Check constraint [EmailPromotion] >= (0) AND [EmailPromotion] <= (2)', @level0type = 'SCHEMA', @level0name = 'Person', @level1type = 'TABLE', @level1name = 'Person', @level2type = 'CONSTRAINT', @level2name = 'CK_Person_EmailPromotion';
GO

The text eventually is cut off as the maximum characters SSMS will display is 8192, but we get a good taste of the output, how it is formatted, and how our metadata collection allowed us to ultimately recreate schema programmatically. For all but testing/display purposes, SELECT results, rather than print them. This will be especially useful when returning larger volumes of information, such as for an entire schema or database.

文本最终被截断，因为SSMS将显示的最大字符数为8192，但是我们对输出，它的格式以及元数据集合如何使我们最终以编程方式重新创建模式有很好的了解。除了测试/显示目的外，请选择SELECT结果，而不是打印它们。当返回大量信息时（例如对于整个模式或数据库），这将特别有用。

客制化 (Customization)

The solution presented here is a proof-of-concept that, with a bit of data analysis and collection, we can turn that metadata into schema creation scripts. It is by no means a complete solution – many objects and attributes were left out, such as compression, replication, partitioning, views, functions, and more.

这里介绍的解决方案是一种概念验证，通过少量的数据分析和收集，我们可以将元数据转换为架构创建脚本。这绝不是一个完整的解决方案–遗漏了许多对象和属性，例如压缩，复制，分区，视图，函数等。

添加更多对象 (Add More Objects)

Adding more objects into, or modifying this process, is not difficult, though. In order to add anything new to this script, the steps are straightforward:

但是，添加更多对象或修改此过程并不困难。为了向此脚本添加任何新内容，这些步骤很简单：

Add data collection for new objects or attributes.
添加新对象或属性的数据收集。
Generate ALTER/CREATE statements for those new objects.
为这些新对象生成ALTER / CREATE语句。
Add those statements (in the correct order) to the final PRINT/SELECT statement.
将这些语句（以正确的顺序）添加到最终的PRINT / SELECT语句中。

While there is certainly still work involved in this process, it becomes easier and easier with each change that is made. Include what your business needs dictate, and leave out whatever is not needed. The intention of a customizable solution is that it can be molded to your distinct needs. Alternatively, you could continue adding objects until it becomes a very complete solution for most business needs, regardless of where you or the script is needed.

尽管此过程中肯定仍有工作要做，但每次进行更改都会变得越来越容易。包括您的业务需求所规定的内容，并忽略不需要的内容。可定制解决方案的目的是可以根据您的不同需求进行模制。另外，您可以继续添加对象，直到它成为满足大多数业务需求的非常完整的解决方案为止，无论您在何处或需要使用脚本。

可选组件 (Optional Components)

A single parameter was added that allowed extended properties to be optional. Making other objects optional would be very similar. Add a BIT parameter to the stored procedure and use it to skip any parts of the stored procedure that deal with the objects in question. If your goal is to simply replicate tables, but to leave off keys, constraints, triggers, etc, which is common in data validation, the disabling those objects’ creation could be very useful.,

添加了一个参数，该参数允许扩展属性为可选。将其他对象设置为可选对象将非常相似。在存储过程中添加一个BIT参数，并使用它跳过存储过程中涉及所讨论对象的任何部分。如果您的目标只是简单地复制表，而忽略键，约束，触发器等（这在数据验证中很常见），那么禁用这些对象的创建可能会非常有用。

操作顺序 (Order of Operations)

The order in which objects print out is somewhat arbitrary. Obviously, the table and associated columns must go first, but after that, whether you create foreign keys or check constraints first is up to you. If you have a preferred order of object creation, then simply adjust the output order at the end of the script.

对象的打印顺序有些随意。显然，表和关联的列必须先行，但是此后，是否创建外键还是首先检查约束由您决定。如果您有对象创建的首选顺序，则只需在脚本末尾调整输出顺序即可。

参数处理 (Parameter Processing)

The parameters are passed in as exact matches, ie: provide a schema and table and get that exact schema and table only. An alternative is to add wildcards to parameters and return any objects that are similar to them. This may be useful if you want all objects that are similar in name to something, or share part of their names.

参数以完全匹配的方式传入，即：提供一个模式和表，并仅获取该确切的模式和表。另一种选择是将通配符添加到参数并返回与它们相似的任何对象。如果您想要名称与某物相似的所有对象或共享其部分名称，这可能很有用。

Additionally, other factors could be taken into account, such as column names, object names, or other methods of filtering. Modify parameters as you see fit—the script provided fills one use case, but could be modified to fit many, many others.

此外，可以考虑其他因素，例如列名，对象名或其他过滤方法。根据需要修改参数-提供的脚本可以满足一个用例，但可以对其进行修改以适应许多其他情况。

结论 (Conclusion)

Schema documentation can be an extremely useful tool when developing new features, testing existing ones, or when maintaining schema in source control. This project is one that began with the introduction, manipulation, and storage of data from a variety of system views. It ended with the organization of that data into creation scripts that could be used to quickly document or replicate database objects quickly & efficiently.

在开发新功能，测试现有功能或在源代码管理中维护架构时，架构文档可能是非常有用的工具。这个项目是从各种系统视图中引入，操作和存储数据开始的。最终，该数据被组织到创建脚本中，这些脚本可用于快速，高效地记录或复制数据库对象。

Since the resulting script was quite large, it is attached to this article in its entirety. Feel free to download, modify, test, and implement it as you see fit. This is another example of how system metadata can be used to accomplish what may initially seem to be a daunting task. Enjoy the results, and let me know if you find any new or interesting applications of this!

由于生成的脚本很大，因此将其全文附在本文中。随意下载，修改，测试和实施您认为合适的工具。这是如何使用系统元数据来完成最初看似艰巨的任务的另一个示例。享受结果，如果您发现任何新的或有趣的应用，请告诉我！

You can download the script here

您可以在此处下载脚本

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

This script is entirely a logical extension of previous articles that have discussed system views and applications, as well as some liberal use of dynamic SQL and string manipulation. Please see these additional articles for discussions on these topics:
Searching SQL Server made easy – Searching catalog views
Searching SQL Server made easy – Building the perfect search script

该脚本完全是先前文章的逻辑扩展，这些文章讨论了系统视图和应用程序，以及一些自由使用动态SQL和字符串操作的方法。请参阅这些其他文章，以获取有关这些主题的讨论：
轻松搜索SQL Server –搜索目录视图
 轻松搜索SQL Server –构建完美的搜索脚本

Also see part 1 of this article for a more direct introduction to this topic:
Introducing schema documentation in SQL Server

另请参阅本文的第1部分，以更直接地介绍此主题：
在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/creating-the-perfect-schema-documentation-script/

脚本文档

culuo4781

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
脚本文档_创建完美的架构文档脚本

脚本文档描述 (Description) System views allow us to gain access to information about any objects within SQL Server, from tables to extended properties to check constraints. This information can be col...
复制链接

扫一扫