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顯示了許多引用它的對象。

圖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 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正版授權，或了解更多產品信息請點擊