SQL语法提示工具SQL Prompt，如何智能重命名和拆分

SQL Prompt根据数据库的对象名称、语法和代码片段自动进行检索，为用户提供合适的代码选择。自动脚本设置使代码简单易读--当开发者不大熟悉脚本时尤其有用。SQL Prompt安装即可使用，能大幅提高编码效率。此外，用户还可根据需要进行自定义，使之以预想的方式工作。本教程演示了SQL Prompt如何显著地减少偶尔出现的“重量级”数据库重构过程所带来的痛苦，例如重命名模块、表和列（智能重命名）或拆分表（拆分表）。

SQL Prompt提供的许多工具都是您每天编写T-SQL代码时都会或多或少使用的工具。SQL Prompt中的重构工具更像是您在沙漠中进行长时间远足时所使用的snakebite工具包中的工具。您希望不必经常使用它们，但是当您使用它们时，它们将非常有价值。一个不太常见但较难的需求是更改对象的“公共接口”，例如通过更改对象或列的名称，甚至通过拆分表来实现更好的设计。

智能重命名

在SSMS对象资源管理器中选择了一个对象后，SQL Prompt的“智能重命名”向导将生成一个脚本来重命名该对象，并修改引用重命名对象的对象。将以正确的顺序进行修改以维护数据库的完整性。

由于数据库中可能存在所有依赖项，因此更改代码对象、表或列的名称可能是一项费力甚至是艰巨的任务。在所有代码和约束中，您必须确保了解一项看似简单的更改的所有可能的副作用。合理地，手动进行这些更改可能只需要几个小时，但是谁有几个小时呢？

SQL Server提供了一些工具来帮助您发现依赖关系，例如sys.sql_expression_dependencies目录视图，或者您可以在SSMS中使用对象依赖关系查看器，只需右键单击对象，然后选择“查看依赖项”，尽管UI有点依靠细节。

另外，Redgate的SQL Dependency Tracker工具与SSMS集成在一起，并为任何选定对象提供详细的依赖关系图。例如，在SSMS对象资源管理器中，右键单击Purchasing.PurchaseOrders，在WideWorldImporters数据库中，选择“查看依赖关系图[对象] ...“。图1显示了许多引用它的对象。

图1

如果您需要手动更改名称，此图表明您要完成的任务的艰巨性。幸运的是，我们可以使用SQL Prompt的智能重命名功能，该功能将自动修改当前数据库中几乎所有对重命名对象的引用。动态SQL引用将不被处理，因此此功能不会消除对可靠测试计划的需要。

我们将从最简单的数据库重构任务开始，重命名代码模块，然后逐步提高复杂性和风险性，重命名表，最后重命名列。

重命名代码对象

假设您编写了一个新的存储过程，Purchasing.PurchaseOrder$ListFinalized该存储过程调用了一个现有的存储过程Purchasing.PurchaseOrder$List，以获取仅包含最终定单的结果集。

CREATE PROCEDURE Purchasing.PurchaseOrder$List
  (
     @IsOrderFinalized bit
  ) AS
  BEGIN
      SELECT PurchaseOrders.PurchaseOrderID, 
             PurchaseOrders.OrderDate, 
             PurchaseOrders.IsOrderFinalized
      FROM   Purchasing.PurchaseOrders
      WHERE  IsOrderFinalized = @IsOrderFinalized;
  END;
  GO
  CREATE PROCEDURE Purchasing.PurchaseOrder$ListFinalized
  AS
  BEGIN
      EXEC Purchasing.[PurchaseOrder$List] 
                                    @IsOrderFinalized = 1;
  END;

清单1

现在，您决定需要将现有Purchasing.PurchaseOrder$List过程的名称更改为PurchaseOrder$ListAll，以阐明它将返回所有采购订单，无论它们是否已完成。

在对象资源管理器中选择：如果您已经在对象资源管理器中打开服务器，则可以在查询窗口中右键单击名称，然后选择“在对象资源管理器中选择”。如果自创建对象以来尚未刷新列表，则可能只会使您靠近列表中的对象。

在SSMS对象资源管理器中找到存储过程之后，您可以通过按F2或右键单击并选择Rename来对其进行重命名，但是所有要做的就是对对象进行重命名，因此任何仍通过其旧名称引用该对象的现有代码都将对其进行重命名，现在都将失败。

消息2812，级别16，状态62，过程购买。PurchaseOrder$ ListFinalized，第4行

  找不到存储过程“Purchasing.PurchaseOrder $ List”。

相反，我们将使用SQL Prompt的智能重命名功能。Purchasing.PurchaseOrder$List在对象资源管理器中右键单击，然后选择“智能重命名”。在对话框中将名称更改为PurchaseOrder$ListAll，如图2所示。

图2

单击“下一步”，您将看到SQL Prompt将执行的任务列表，以重命名对象并调整按名称引用该对象的所有相关对象。

• 放下程序 [Purchasing].[PurchaseOrder$List]

• 建立程序 [Purchasing].[PurchaseOrder$ListAll]

• 变更程序 [Purchasing].[PurchaseOrder$ListFinalized]

执行生成的脚本，SQL Prompt将进行更改。如果有错误，脚本将失败，并将回滚所有更改。

重命名表

虽然更改编码模块的名称通常很容易，但是更改表和列的名称需要更多注意，并且您需要仔细检查生成的脚本，以便您确切知道它在做什么。有时由于某些对象在SQL Server中使用的功能，该过程无法修改某些对象，因此您需要手动干预和修改生成的脚本。

简单的表重命名

假设出于某种奇怪的原因，我们希望将Purchasing.PurchaseOrders表重命名为Purchasing.ThePurchaseOrders。右键单击表然后选择Smart Rename。将名称更改为ThePurchaseOrders，然后单击下一步。SQL Prompt列出了所有必需的操作，以解决所有依赖性（如图1所示）。

图3

单击查看脚本以查看它将执行的脚本，其中包括更改我们的存储过程，Purchasing.PurchaseOrder$ListAll以引用新的表名。

ALTER PROCEDURE Purchasing.[PurchaseOrder$ListAll]
  (
     @IsOrderFinalized bit
  ) AS
  BEGIN
      SELECT ThePurchaseOrders.PurchaseOrderID, 
             ThePurchaseOrders.OrderDate, 
             ThePurchaseOrders.IsOrderFinalized
      FROM   Purchasing.ThePurchaseOrders
      WHERE  IsOrderFinalized = @IsOrderFinalized;
  END;

清单2

执行该脚本，您将看到一组PRINT语句，将其告知您所做的每个更改。

智能重命名的局限性

对于大多数表，“智能重命名”实际上非常神奇，但确实有一些局限性需要我们证明。幸运的是，WideWorldImporters为我们提供了一些需要更改的表，例如Application.Cities、具有表绑定的访问、时间扩展和行级安全性，我们将需要手动处理所有这些表。

假设我们要给Application.Cities表重新命名。同样，只需右键单击表格并选择Smart Rename即可。但是，由于依赖对象引用了我们建议更改的对象，因此现在您将看到更长的操作列表。

图4

如果您尝试执行脚本，它将失败。第一个错误是由于尝试重命名Cities为TheCities而引起的，错误如下。生成的脚本会使用IF @@ERROR <> 0 SET NOEXEC ON，因此后续步骤将无法运行，从而导致进一步的多余错误，此处未显示。

消息15336，级别16，状态1，过程sp_rename，第565行 

  无法重命名对象“ [Application]。[Cities]”，因为该对象参与了强制性依赖性。

这说明了智能重命名功能的局限性。生成的脚本仅使用对sp_rename存储过程的调用，但这不适用于每个表。例如，此处在时间表（例如Application.Cities）上不支持此操作，因此它将不起作用。

为了避免这种错误，你需要的代码块重新编码这段代码来修改Application.Cities表以关闭系统版本，更改表的名称（也可能是其相关的历史表，Application.Cities_Archive(History)以保持清晰），然后重新启用系统版本控制。

然而，在这种情况下，还存在进一步的复杂性。该WideWorldImporters数据库实现行级安全性，这是使用安全策略来实现的。这些策略之一FilterCustomersBySalesTerritoryRole包含谓词，该谓词引用了一个内联表值函数（iTVF）Application.DetermineCustomerAccess，该函数称为Application.Cities表。此iTVF使用架构绑定，这意味着我们不能在仍被安全策略引用它的同时对其进行更改或删除，但是我们需要对其进行更改，因为它引用了Application.Cities要重命名的表。

如您所见，这种情况可能会导致大量要求手动进行的更改。我们将需要更改安全策略，以删除引用iTVF的谓词，以便我们随后可以删除iTVF，以便可以禁用系统版本控制，然后可以重命名表。完成后，我们将需要重新启用系统版本控制，重新创建iTVF并重新建立有效的安全策略。

--Original code:
  --EXEC sp_rename N'[Application].[Cities]', N'TheCities', N'OBJECT'
  GO
  --Replaced with:
  -- Take off row level security
  PRINT N'Altering [Application].[DetermineCustomerAccess]'
  GO
  ALTER SECURITY POLICY [Application].[FilterCustomersBySalesTerritoryRole]
  DROP FILTER PREDICATE
  ON [Sales].[Customers]
  GO
  IF @@ERROR <> 0 SET NOEXEC ON
  GO
  ALTER SECURITY POLICY [Application].[FilterCustomersBySalesTerritoryRole]
  DROP BLOCK PREDICATE
  ON [Sales].[Customers] AFTER UPDATE
  GO
  IF @@ERROR <> 0 SET NOEXEC ON
  GO
  -- Deal with the schema bound objects. You could change to 
  -- a blank function and let the later steps ALTER the function
  -- but we need this to reapply row-level security
  DROP FUNCTION Application.DetermineCustomerAccess
  GO
  IF @@ERROR <> 0 SET NOEXEC ON
  GO
  PRINT N'Renaming table, and handling system version table'
  GO
  -- Remove system versioning 
  ALTER TABLE Application.Cities SET (SYSTEM_VERSIONING = OFF)
  GO
  IF @@ERROR <> 0 SET NOEXEC ON
  GO
  -- Now rename the column
  EXEC sp_rename N'[Application].[Cities]', N'TheCities', N'OBJECT'
  GO
  IF @@ERROR <> 0 SET NOEXEC ON
  GO
  EXEC sp_rename N'[Application].[Cities_Archive]', N'TheCities_Archive', N'OBJECT'
  IF @@ERROR <> 0 SET NOEXEC ON
  GO
  -- turn back on temporal extensions. Rename temporal table if
  -- desired
  ALTER TABLE Application.TheCities SET    
  (   
  SYSTEM_VERSIONING = ON (HISTORY_TABLE = Application.Cities_Archive)   
  );   
  GO
  IF @@ERROR <> 0 SET NOEXEC ON
  GO
  --Add back the function, and manually change the name
  --of the Cities table to TheCities
  CREATE FUNCTION [Application].[DetermineCustomerAccess](@CityID int)
  RETURNS table
  WITH SCHEMABINDING
  AS
  RETURN (SELECT 1 AS Acce***esult
          WHERE IS_ROLEMEMBER(N'db_owner') <> 0
          OR IS_ROLEMEMBER((SELECT sp.SalesTerritory
                            FROM [Application].TheCities AS C
                            INNER JOIN [Application].StateProvinces AS sp
                            ON C.StateProvinceID = sp.StateProvinceID
                            WHERE C.CityID = @CityID) + N' Sales') <> 0
          OR (ORIGINAL_LOGIN() = N'Website'
              AND EXISTS (SELECT 1
                          FROM [Application].TheCities AS C
                          INNER JOIN [Application].StateProvinces AS sp
                          ON C.StateProvinceID = sp.StateProvinceID
                          WHERE C.CityID = @CityID
                          AND sp.SalesTerritory = SESSION_CONTEXT(N'SalesTerritory'))));
  GO
  -- Turn back on row-level security
  IF @@ERROR <> 0 SET NOEXEC ON
  GO
  ALTER SECURITY POLICY [Application].[FilterCustomersBySalesTerritoryRole] 
  ADD FILTER PREDICATE [Application].[DetermineCustomerAccess]([DeliveryCityID]) ON [Sales].[Customers],
  ADD BLOCK PREDICATE [Application].[DetermineCustomerAccess]([DeliveryCityID]) ON [Sales].[Customers] AFTER UPDATE;
  GO
  IF @@ERROR <> 0 SET NOEXEC ON
  GO
  ALTER SECURITY POLICY [Application].[FilterCustomersBySalesTerritoryRole] WITH (STATE = ON);
  GO
  IF @@ERROR <> 0 SET NOEXEC ON
  GO

清单3

显然，这是一项艰巨的任务，但是我们为您处理的所有对象更改，除了架构绑定功能，系统版本控制和行级安全性之外。这些更改大多数都不是您遇到的表的常态，但是您偶尔需要处理每种情况。

提示：除了进行数据库更改（在进行结构更改（如重命名对象）时应始终具有的数据库备份）外，最好使用另一个SQL Toolbelt工具：SQL Compare。进行任何更改之前，使用它来捕获数据库中代码的快照，然后在更改完成后将数据库与快照进行比较。这将使您无需使用备份就可以查找您没有想到的任何更改。例如，如果您删除了架构绑定的对象，则可能已失去该对象的安全性。看到失败的部署后没有任何变化也很令人欣慰，因为您没有意识到自己必须首先处理行级安全性！

尽管如此，对于代码的公共接口，重命名表是相对安全的任务。表名通常不会出现在查询的输出中，因此，如果所有访问都是通过存储过程或视图进行的，则进行安全更改。但是，重命名列是一个完全不同的故事。

重命名列

想象一下，一个项目进行了两周，您已经编写了许多T-SQL编码的对象、视图、触发器、过程、约束等，然后突然意识到该Product表的列被拼写为ProductNmber。您需要在发布前进行更改。我已经失去了完成一组表或新列的构建次数的计数，然后才意识到我拼错了“hybid”或“soliciation”。当然，尽管我喜欢SQL Prompt的代码完成功能，但它会像“混合”一样轻松地自动填充“混合”，因此您可能要等到代码审查时才注意到错误。

例如，我们将对OrderDate新重命名的ThePurchaseOrders表中的列进行更改。我们的Purchasing.PurchaseOrder$ListAll存储过程返回PurchaseUserID，OrderDate和IsOrderFinalized列。换句话说，这三列是接口的一部分。

CREATE PROCEDURE Purchasing.PurchaseOrder$ListAll
  (
      @IsOrderFinalized bit
  ) AS
  BEGIN
      SELECT ThePurchaseOrders.PurchaseOrderID,
             ThePurchaseOrders.OrderDate, 
                     ThePurchaseOrders.IsOrderFinalized
      FROM   Purchasing.ThePurchaseOrders
      WHERE  IsOrderFinalized = @IsOrderFinalized;
  END

清单4

如果要重命名表中的这些列之一，可以再次使用Smart Rename。就像表格示例一样，右键单击OrderDateSSMS对象资源管理器中的列，然后将其重命名为OrderDate2。SQL提示会找到所有引用此列的对象，包括该Purchasing.PurchaseOrder$ListAll 过程，并且生成的脚本会相应地对其进行更新。

SELECT ThePurchaseOrders.PurchaseOrderID, 
                 ThePurchaseOrders.OrderDate2, 
                     ThePurchaseOrders.IsOrderFinalized
      FROM   Purchasing.ThePurchaseOrders
      WHERE  IsOrderFinalized = @IsOrderFinalized;

清单5

但是，这意味着此过程的用户现在将看到OrderDate2，而不是OrderDate。如果这是一个新的开发，并且还没有人开始使用该代码，那么这并不是真正的问题，但是如果您需要用户的观点保持不变，则需要修复该代码。如果原始查询使用了别名，这种问题将很容易避免，如清单6所示，因为现在对列名进行的任何后续更改都不会影响该公共接口。

SELECT ThePurchaseOrders. PurchaseOrderID AS PurchaseOrderID, 
                 ThePurchaseOrders.OrderDate AS OrderDate, 
                     ThePurchaseOrders.IsOrderFinalized AS IsOrderFinalized
      FROM   Purchasing.ThePurchaseOrders
      WHERE  IsOrderFinalized = @IsOrderFinalized;

清单6

真正的担心是，除非您虔诚地使用别名，否则最终可能会因接口更改而混合了接口更改的地方和接口没有更改的地方。由于将显示用于更改列的实际脚本，因此您可以非常容易地在脚本上使用“查找”来确定要更改的内容。

拆分表

通过在SSMS对象资源管理器中选择一个对象，SQL Prompt的“拆分表”向导旨在生成一个脚本，该脚本创建链接表、修改原始表以及修改引用主表的任何对象。检查脚本后，您可以执行它。SQL Prompt将所有更改包装在一个事务中，因此可以将它们回滚以防万一发生任何错误。

您将不需要将现有表一分为二，也不必冒着被破坏的风险，但是当您这样做时，SQL Prompt的“拆分表”功能可以节省大量时间和精力。我考虑这样做的主要原因是出于性能原因，“隔离”现有表中的非常大的列，但有时只是为了方便起见。

例如，假设我们要向Purchasing.ThePurchaseOrders表中添加系统版本控制。我们只想将版本历史记录保留在该OrderDate2列中。实际上，即使我们想对表格的大部分内容进行版本控制，也可能不想在两个nvarchar(max)列上保留历史记录，因为每次更新都会创建一个新的文本副本，副本最多可包含2GB的文本。

因此，我们的目标是将OrderDate2列移出ThePurchaseOrders表并移到新表中，然后可以对其应用系统版本控制。右键单击ThePurchaseOrders表，选择“拆分表”，将出现向导。为新的辅助表命名，如ThePurchaseOrdersTemporal，然后在下一个屏幕上，将复制键PurchaseOrderId，然后将OrderDate2移动到新表中，如图5所示。两个表都将具有主键，因此不能有重复的行在两个表中。

图5

下一个屏幕将要求您创建一个外键，该外键确定了将数据插入这些表的顺序。

图6

这样可确保添加到Purchasing.ThePurchaseOrders表中的任何行都引用的PurchaseOrderID列中的现有值Purchasing.ThePurchaseOrdersTemporal。

单击“下一步”，您将看到有关将要执行的操作，所做更改的依赖性以及与所需操作有关的所有警告的信息。在这种情况下，我们会看到警告，它不能处理非标准文件组，也不能保证在从父表中删除列时不能自动保留数据（尽管在这种情况下，生成的脚本会将您的数据保存在新表中）。

图7

当然，您总是希望检查和微调这些生成的脚本之一。SQL Prompt可能不会每次都正确。您需要确保该工具所做的更改符合您计划使用数据的方式。例如，该OrderDate2列定义为NOT NULL。但是，既然该列在相关表中，则该列在技术上可以为空，因为您不能强制使用1-1关系。

单击查看脚本按钮将使该工具生成一个脚本，您可以使用该脚本来应用更改。它将创建新表及其主键，将数据加载到新创建的表中，从原始表中删除该列，更改所有相关对象，添加FOREIGN KEY并最终在新表的列上建立扩展属性。

将更改所有从属对象以解决新的架构设计。 例如，修改了PurchaseOrder $ ListAll过程以替换对Purchasing.ThePurchaseOrders的引用，并在Purchasing.ThePurchaseOrders和Purchasing.ThePurchaseOrdersTemporal之间使用INNER JOIN进行替换，如清单7所示。

这是INNER JOIN因为期望这两行都是必需的，因为它们将一起成为表的一部分。即使您只选择了允许NULL值的列也是如此（您可能不希望如此，因此请单独检查每种情况并相应地更改代码）。

ALTER PROCEDURE Purchasing.[PurchaseOrder$ListAll]
  (
      @IsOrderFinalized bit
  ) AS
  BEGIN
      SELECT ThePurchaseOrders.PurchaseOrderID, 
             ThePurchaseOrdersTemporal.OrderDate, 
                     ThePurchaseOrders.IsOrderFinalized
      FROM   <strong>(Purchasing.ThePurchaseOrders INNER JOIN   </strong>
  <strong>            Purchasing.ThePurchaseOrdersTemporal ON </strong>
  <strong>                  ThePurchaseOrders.purchaseorderid=</strong>
  <strong>                  ThePurchaseOrdersTemporal.purchaseorderid)</strong>
      WHERE  IsOrderFinalized = @IsOrderFinalized;
  END;

清单7

与修改表并可能发生数据丢失的任何过程一样，强烈建议您检查生成的脚本，并在数据库结构的副本上至少测试一次部署，如果脚本中的内容有误，请进行备份。一旦完全满意，就可以运行脚本，然后将时间扩展应用于Purchasing.ThePurchaseOrdersTemporal表，而不是原始表。

最后提醒您测试您的代码，并确保所有代码在SQL Server对象和用户界面中均按预期工作。您正在极大地改变对象与外界的接口。

结论

在本教程中，我们研究了SQL Prompt中两个最少使用的功能，但是在您需要它们时它们是无价的。如果您必须重命名对象或列，甚至将一个表拆分为两个表，毫无疑问，智能重命名和拆分表功能可以为您节省大量时间，特别是如果您已实现SQL Server使用以服务器为中心的范例的数据库，其中包含约束、触发器和存储过程。

您可以用更少的精力来进行大规模的名称和结构更改，这意味着您可以投入更多的时间和精力来测试应用程序在重构后将继续按预期正常运行。