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使用以服務器為中心的范例的數據庫，其中包含約束、觸發器和存儲過程。

您可以用更少的精力來進行大規模的名稱和結構更改，這意味著您可以投入更多的時間和精力來測試應用程序在重構后將繼續按預期正常運行。