logo SQL Prompt教程 我也要发布文档

SQL语法提示工具SQL Prompt教程:使用SQL Prompt重构数据库(上)


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显示了许多引用它的对象。

使用SQL Prompt重构数据库

图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所示。

使用SQL Prompt重构数据库

图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所示)。

使用SQL Prompt重构数据库

图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即可。但是,由于依赖对象引用了我们建议更改的对象,因此现在您将看到更长的操作列表。

使用SQL Prompt重构数据库

图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 AccessResult
          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

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

智能重命名的内容到这里就完结啦,后面将会更新该教程的后半部分内容——拆分表,感兴趣的朋友可以继续关注哦~也可以下载SQL Prompt免费版尝试一下~

相关内容推荐:

SQL Prompt系列教程>>>


想要购买SQL Prompt正版授权,或了解更多产品信息请点击“咨询在线客服”