我有一個包含數千行程式碼的文件，其索引詳細資訊如下：

IF NOT EXISTS (SELECT 1 FROM SYS.INDEXES WITH (NOLOCK) WHERE name = 'IX_Test1' AND [object_id] = OBJECT_ID(N'Test1', N'U'))  
BEGIN  
CREATE UNIQUE INDEX [IX_Test1] ON [Test1]  
(  
[cd] ASC   
)   
WITH (FILLFACTOR = 90,  IGNORE_DUP_KEY = OFF,  PAD_INDEX = OFF,  ALLOW_ROW_LOCKS = ON,  ALLOW_PAGE_LOCKS = ON,  STATISTICS_NORECOMPUTE = OFF,  DROP_EXISTING = OFF ) ON [PRIMARY]  
END  


IF NOT EXISTS (SELECT 1 FROM SYS.INDEXES WITH (NOLOCK) WHERE name = 'IX_Test2' AND [object_id] = OBJECT_ID(N'Test2', N'U'))  
BEGIN  
CREATE INDEX [IX_Test2] ON [Test2]  
(  
[AdID] ASC , [Date] ASC   
)  INCLUDE ( [AID], [LM] )   
WITH (FILLFACTOR = 90,  IGNORE_DUP_KEY = OFF,  PAD_INDEX = OFF,  ALLOW_ROW_LOCKS = ON,  ALLOW_PAGE_LOCKS = ON,  STATISTICS_NORECOMPUTE = OFF,   DROP_EXISTING = OFF ) ON [PRIMARY]  
END

我想像下面這樣格式化這些數據以進行比較：

IF NOT EXISTS (SELECT 1 FROM SYS.INDEXES WITH (NOLOCK) WHERE name = 'IX_Test1' AND [object_id] = OBJECT_ID(N'Test1', N'U'))  BEGIN   CREATE UNIQUE INDEX [IX_Test1] ON [Test1]  (  [cd] ASC   )    WITH (FILLFACTOR = 90,  IGNORE_DUP_KEY = OFF,  PAD_INDEX = OFF,  ALLOW_ROW_LOCKS = ON,  ALLOW_PAGE_LOCKS = ON,  STATISTICS_NORECOMPUTE = OFF,  DROP_EXISTING = OFF ) ON [PRIMARY]  END   
IF NOT EXISTS (SELECT 1 FROM SYS.INDEXES WITH (NOLOCK) WHERE name = 'IX_Test2' AND [object_id] = OBJECT_ID(N'Test2', N'U'))  BEGIN   CREATE INDEX [IX_Test2] ON [Test2]  (  [AdID] ASC , [Date] ASC   )  INCLUDE ( [AID], [LM] )    WITH (FILLFACTOR = 90,  IGNORE_DUP_KEY = OFF,  PAD_INDEX = OFF,  ALLOW_ROW_LOCKS = ON,  ALLOW_PAGE_LOCKS = ON,  STATISTICS_NORECOMPUTE = OFF,   DROP_EXISTING = OFF ) ON [PRIMARY]  END

所以基本上 IF 和 END 之間的所有內容都應該排成一行。我嘗試使用 REGEX 來完成它，但沒有得到它的工作。如果有人可以在這方面幫助我，那將是很大的幫助。

如果您需要更多詳細資訊，請告訴我。

如果您願意分兩步執行此操作，則可以使用 SSMS 替換對話框中的 Regex 選項。

第1步

刪除所有行尾，將其全部壓縮為一行。

Replace: \r\n

With:

解釋：\r\n查找行結尾，幾乎可以肯定是輸入 (\r) 後跟換行 (\n)。什麼都不換。

第2步

將所有出現的“END”替換為“END”加上一個新的行尾。

Replace: END

With: END\r\n

說明：將“END”替換為“END”加輸入加換行。

古怪

您上面的範例在第一個“END”之後有兩個空格，第二個之後沒有空格。這將導致第 2 步之後的結果如下所示：

IF NOT EXISTS...
 IF NOT EXISTS...

如果您的真實程式碼也有這種奇怪之處，您可以通過確保“END”之後沒有空格來解決這個問題，步驟 0 為：

Replace: END（“END”後兩個空格）

With: END（“END”後沒有空格）

在 SSMS 17.9.1 中測試。

引用自：https://dba.stackexchange.com/questions/238063