SQL語(yǔ)法提示工具SQL Prompt教程：使用SQL Prompt重構(gòu)數(shù)據(jù)庫(kù)（上）

SQL Prompt根據(jù)數(shù)據(jù)庫(kù)的對(duì)象名稱、語(yǔ)法和代碼片段自動(dòng)進(jìn)行檢索，為用戶提供合適的代碼選擇。自動(dòng)腳本設(shè)置使代碼簡(jiǎn)單易讀--當(dāng)開(kāi)發(fā)者不大熟悉腳本時(shí)尤其有用。SQL Prompt安裝即可使用，能大幅提高編碼效率。此外，用戶還可根據(jù)需要進(jìn)行自定義，使之以預(yù)想的方式工作。

點(diǎn)擊下載SQL Prompt正式版

本教程演示了SQL Prompt如何顯著地減少偶爾出現(xiàn)的“重量級(jí)”數(shù)據(jù)庫(kù)重構(gòu)過(guò)程所帶來(lái)的痛苦，例如重命名模塊、表和列（智能重命名）或拆分表（拆分表）。由于該教程內(nèi)容比較多，分為上下兩個(gè)部分呢，這篇文章是該教程的上半部分——智能重命名。

SQL Prompt提供的許多工具都是您每天編寫(xiě)T-SQL代碼時(shí)都會(huì)或多或少使用的工具。SQL Prompt中的重構(gòu)工具更像是您在沙漠中進(jìn)行長(zhǎng)時(shí)間遠(yuǎn)足時(shí)所使用的snakebite工具包中的工具。您希望不必經(jīng)常使用它們，但是當(dāng)您使用它們時(shí)，它們將非常有價(jià)值。一個(gè)不太常見(jiàn)但較難的需求是更改對(duì)象的“公共接口”，例如通過(guò)更改對(duì)象或列的名稱，甚至通過(guò)拆分表來(lái)實(shí)現(xiàn)更好的設(shè)計(jì)。

智能重命名

在SSMS對(duì)象資源管理器中選擇了一個(gè)對(duì)象后，SQL Prompt的“智能重命名”向?qū)⑸梢粋€(gè)腳本來(lái)重命名該對(duì)象，并修改引用重命名對(duì)象的對(duì)象。將以正確的順序進(jìn)行修改以維護(hù)數(shù)據(jù)庫(kù)的完整性。

由于數(shù)據(jù)庫(kù)中可能存在所有依賴項(xiàng)，因此更改代碼對(duì)象、表或列的名稱可能是一項(xiàng)費(fèi)力甚至是艱巨的任務(wù)。在所有代碼和約束中，您必須確保了解一項(xiàng)看似簡(jiǎn)單的更改的所有可能的副作用。合理地，手動(dòng)進(jìn)行這些更改可能只需要幾個(gè)小時(shí)，但是誰(shuí)有幾個(gè)小時(shí)呢？

SQL Server提供了一些工具來(lái)幫助您發(fā)現(xiàn)依賴關(guān)系，例如sys.sql_expression_dependencies目錄視圖，或者您可以在SSMS中使用對(duì)象依賴關(guān)系查看器，只需右鍵單擊對(duì)象，然后選擇“查看依賴項(xiàng)”，盡管UI有點(diǎn)依靠細(xì)節(jié)。

另外，Redgate的SQL Dependency Tracker工具與SSMS集成在一起，并為任何選定對(duì)象提供詳細(xì)的依賴關(guān)系圖。例如，在SSMS對(duì)象資源管理器中，右鍵單擊Purchasing.PurchaseOrders，在WideWorldImporters數(shù)據(jù)庫(kù)中，選擇“查看依賴關(guān)系圖[對(duì)象] ...“。圖1顯示了許多引用它的對(duì)象。

圖1

如果您需要手動(dòng)更改名稱，此圖表明您要完成的任務(wù)的艱巨性。幸運(yùn)的是，我們可以使用SQL Prompt的智能重命名功能，該功能將自動(dòng)修改當(dāng)前數(shù)據(jù)庫(kù)中幾乎所有對(duì)重命名對(duì)象的引用。動(dòng)態(tài)SQL引用將不被處理，因此此功能不會(huì)消除對(duì)可靠測(cè)試計(jì)劃的需要。

我們將從最簡(jiǎn)單的數(shù)據(jù)庫(kù)重構(gòu)任務(wù)開(kāi)始，重命名代碼模塊，然后逐步提高復(fù)雜性和風(fēng)險(xiǎn)性，重命名表，最后重命名列。

重命名代碼對(duì)象

假設(shè)您編寫(xiě)了一個(gè)新的存儲(chǔ)過(guò)程，Purchasing.PurchaseOrder$ListFinalized該存儲(chǔ)過(guò)程調(diào)用了一個(gè)現(xiàn)有的存儲(chǔ)過(guò)程Purchasing.PurchaseOrder$List，以獲取僅包含最終定單的結(jié)果集。

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

現(xiàn)在，您決定需要將現(xiàn)有Purchasing.PurchaseOrder$List過(guò)程的名稱更改為PurchaseOrder$ListAll，以闡明它將返回所有采購(gòu)訂單，無(wú)論它們是否已完成。

在對(duì)象資源管理器中選擇：如果您已經(jīng)在對(duì)象資源管理器中打開(kāi)服務(wù)器，則可以在查詢窗口中右鍵單擊名稱，然后選擇“在對(duì)象資源管理器中選擇”。如果自創(chuàng)建對(duì)象以來(lái)尚未刷新列表，則可能只會(huì)使您靠近列表中的對(duì)象。

在SSMS對(duì)象資源管理器中找到存儲(chǔ)過(guò)程之后，您可以通過(guò)按F2或右鍵單擊并選擇Rename來(lái)對(duì)其進(jìn)行重命名，但是所有要做的就是對(duì)對(duì)象進(jìn)行重命名，因此任何仍通過(guò)其舊名稱引用該對(duì)象的現(xiàn)有代碼都將對(duì)其進(jìn)行重命名，現(xiàn)在都將失敗。

消息2812，級(jí)別16，狀態(tài)62，過(guò)程購(gòu)買。PurchaseOrder$ ListFinalized，第4行

  找不到存儲(chǔ)過(guò)程“Purchasing.PurchaseOrder $ List”。

相反，我們將使用SQL Prompt的智能重命名功能。Purchasing.PurchaseOrder$List在對(duì)象資源管理器中右鍵單擊，然后選擇“智能重命名”。在對(duì)話框中將名稱更改為PurchaseOrder$ListAll，如圖2所示。

圖2

單擊“下一步”，您將看到SQL Prompt將執(zhí)行的任務(wù)列表，以重命名對(duì)象并調(diào)整按名稱引用該對(duì)象的所有相關(guān)對(duì)象。

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

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

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

執(zhí)行生成的腳本，SQL Prompt將進(jìn)行更改。如果有錯(cuò)誤，腳本將失敗，并將回滾所有更改。

重命名表

雖然更改編碼模塊的名稱通常很容易，但是更改表和列的名稱需要更多注意，并且您需要仔細(xì)檢查生成的腳本，以便您確切知道它在做什么。有時(shí)由于某些對(duì)象在SQL Server中使用的功能，該過(guò)程無(wú)法修改某些對(duì)象，因此您需要手動(dòng)干預(yù)和修改生成的腳本。

簡(jiǎn)單的表重命名

假設(shè)出于某種奇怪的原因，我們希望將Purchasing.PurchaseOrders表重命名為Purchasing.ThePurchaseOrders。右鍵單擊表然后選擇Smart Rename。將名稱更改為ThePurchaseOrders，然后單擊下一步。SQL Prompt列出了所有必需的操作，以解決所有依賴性（如圖1所示）。

圖3

單擊查看腳本以查看它將執(zhí)行的腳本，其中包括更改我們的存儲(chǔ)過(guò)程，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

執(zhí)行該腳本，您將看到一組PRINT語(yǔ)句，將其告知您所做的每個(gè)更改。

智能重命名的局限性

對(duì)于大多數(shù)表，“智能重命名”實(shí)際上非常神奇，但確實(shí)有一些局限性需要我們證明。幸運(yùn)的是，WideWorldImporters為我們提供了一些需要更改的表，例如Application.Cities、具有表綁定的訪問(wèn)、時(shí)間擴(kuò)展和行級(jí)安全性，我們將需要手動(dòng)處理所有這些表。

假設(shè)我們要給Application.Cities表重新命名。同樣，只需右鍵單擊表格并選擇Smart Rename即可。但是，由于依賴對(duì)象引用了我們建議更改的對(duì)象，因此現(xiàn)在您將看到更長(zhǎng)的操作列表。

圖4

如果您嘗試執(zhí)行腳本，它將失敗。第一個(gè)錯(cuò)誤是由于嘗試重命名Cities為TheCities而引起的，錯(cuò)誤如下。生成的腳本會(huì)使用IF @@ERROR <> 0 SET NOEXEC ON，因此后續(xù)步驟將無(wú)法運(yùn)行，從而導(dǎo)致進(jìn)一步的多余錯(cuò)誤，此處未顯示。

消息15336，級(jí)別16，狀態(tài)1，過(guò)程sp_rename，第565行 

  無(wú)法重命名對(duì)象“ [Application]。[Cities]”，因?yàn)樵搶?duì)象參與了強(qiáng)制性依賴性。

這說(shuō)明了智能重命名功能的局限性。生成的腳本僅使用對(duì)sp_rename存儲(chǔ)過(guò)程的調(diào)用，但這不適用于每個(gè)表。例如，此處在時(shí)間表（例如Application.Cities）上不支持此操作，因此它將不起作用。

為了避免這種錯(cuò)誤，你需要的代碼塊重新編碼這段代碼來(lái)修改Application.Cities表以關(guān)閉系統(tǒng)版本，更改表的名稱（也可能是其相關(guān)的歷史表，Application.Cities_Archive(History)以保持清晰），然后重新啟用系統(tǒng)版本控制。

然而，在這種情況下，還存在進(jìn)一步的復(fù)雜性。該WideWorldImporters數(shù)據(jù)庫(kù)實(shí)現(xiàn)行級(jí)安全性，這是使用安全策略來(lái)實(shí)現(xiàn)的。這些策略之一FilterCustomersBySalesTerritoryRole包含謂詞，該謂詞引用了一個(gè)內(nèi)聯(lián)表值函數(shù)（iTVF）Application.DetermineCustomerAccess，該函數(shù)稱為Application.Cities表。此iTVF使用架構(gòu)綁定，這意味著我們不能在仍被安全策略引用它的同時(shí)對(duì)其進(jìn)行更改或刪除，但是我們需要對(duì)其進(jìn)行更改，因?yàn)樗昧薃pplication.Cities要重命名的表。

如您所見(jiàn)，這種情況可能會(huì)導(dǎo)致大量要求手動(dòng)進(jìn)行的更改。我們將需要更改安全策略，以刪除引用iTVF的謂詞，以便我們隨后可以刪除iTVF，以便可以禁用系統(tǒng)版本控制，然后可以重命名表。完成后，我們將需要重新啟用系統(tǒng)版本控制，重新創(chuàng)建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

顯然，這是一項(xiàng)艱巨的任務(wù)，但是我們?yōu)槟幚淼乃袑?duì)象更改，除了架構(gòu)綁定功能，系統(tǒng)版本控制和行級(jí)安全性之外。這些更改大多數(shù)都不是您遇到的表的常態(tài)，但是您偶爾需要處理每種情況。

提示：除了進(jìn)行數(shù)據(jù)庫(kù)更改（在進(jìn)行結(jié)構(gòu)更改（如重命名對(duì)象）時(shí)應(yīng)始終具有的數(shù)據(jù)庫(kù)備份）外，最好使用另一個(gè)SQL Toolbelt工具：SQL Compare。進(jìn)行任何更改之前，使用它來(lái)捕獲數(shù)據(jù)庫(kù)中代碼的快照，然后在更改完成后將數(shù)據(jù)庫(kù)與快照進(jìn)行比較。這將使您無(wú)需使用備份就可以查找您沒(méi)有想到的任何更改。例如，如果您刪除了架構(gòu)綁定的對(duì)象，則可能已失去該對(duì)象的安全性?？吹绞〉牟渴鸷鬀](méi)有任何變化也很令人欣慰，因?yàn)槟鷽](méi)有意識(shí)到自己必須首先處理行級(jí)安全性！

盡管如此，對(duì)于代碼的公共接口，重命名表是相對(duì)安全的任務(wù)。表名通常不會(huì)出現(xiàn)在查詢的輸出中，因此，如果所有訪問(wèn)都是通過(guò)存儲(chǔ)過(guò)程或視圖進(jìn)行的，則進(jìn)行安全更改。但是，重命名列是一個(gè)完全不同的故事。

重命名列

想象一下，一個(gè)項(xiàng)目進(jìn)行了兩周，您已經(jīng)編寫(xiě)了許多T-SQL編碼的對(duì)象、視圖、觸發(fā)器、過(guò)程、約束等，然后突然意識(shí)到該P(yáng)roduct表的列被拼寫(xiě)為ProductNmber。您需要在發(fā)布前進(jìn)行更改。我已經(jīng)失去了完成一組表或新列的構(gòu)建次數(shù)的計(jì)數(shù)，然后才意識(shí)到我拼錯(cuò)了“hybid”或“soliciation”。當(dāng)然，盡管我喜歡SQL Prompt的代碼完成功能，但它會(huì)像“混合”一樣輕松地自動(dòng)填充“混合”，因此您可能要等到代碼審查時(shí)才注意到錯(cuò)誤。

例如，我們將對(duì)OrderDate新重命名的ThePurchaseOrders表中的列進(jìn)行更改。我們的Purchasing.PurchaseOrder$ListAll存儲(chǔ)過(guò)程返回PurchaseUserID，OrderDate和IsOrderFinalized列。換句話說(shuō)，這三列是接口的一部分。

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對(duì)象資源管理器中的列，然后將其重命名為OrderDate2。SQL提示會(huì)找到所有引用此列的對(duì)象，包括該P(yáng)urchasing.PurchaseOrder$ListAll 過(guò)程，并且生成的腳本會(huì)相應(yīng)地對(duì)其進(jìn)行更新。

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

清單5

但是，這意味著此過(guò)程的用戶現(xiàn)在將看到OrderDate2，而不是OrderDate。如果這是一個(gè)新的開(kāi)發(fā)，并且還沒(méi)有人開(kāi)始使用該代碼，那么這并不是真正的問(wèn)題，但是如果您需要用戶的觀點(diǎn)保持不變，則需要修復(fù)該代碼。如果原始查詢使用了別名，這種問(wèn)題將很容易避免，如清單6所示，因?yàn)楝F(xiàn)在對(duì)列名進(jìn)行的任何后續(xù)更改都不會(huì)影響該公共接口。

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

清單6

真正的擔(dān)心是，除非您虔誠(chéng)地使用別名，否則最終可能會(huì)因接口更改而混合了接口更改的地方和接口沒(méi)有更改的地方。由于將顯示用于更改列的實(shí)際腳本，因此您可以非常容易地在腳本上使用“查找”來(lái)確定要更改的內(nèi)容。

智能重命名的內(nèi)容到這里就完結(jié)啦，后面將會(huì)更新該教程的后半部分內(nèi)容——拆分表，感興趣的朋友可以繼續(xù)關(guān)注哦~也可以下載SQL Prompt免費(fèi)版嘗試一下~

相關(guān)內(nèi)容推薦：

SQL Prompt系列教程>>>

想要購(gòu)買SQL Prompt正版授權(quán)，或了解更多產(chǎn)品信息請(qǐng)點(diǎn)擊“咨詢?cè)诰€客服”