使用動態 SQL 在不同的數據庫中創建視圖

作為一個更大項目的一部分，我試圖在 SQL Server 實例中的每個數據庫上創建一個新視圖。

我創建了一個儲存過程，其中包括通過游標遍歷實例中的數據庫，然後嘗試使用sp_executesql. 不幸的是，我試圖在其上創建視圖的數據庫中不存在儲存過程。所以我試圖讓 sql server 使用該數據庫，然後執行創建視圖腳本。當我執行它時，我收到錯誤

‘CREATE VIEW’ 必須是查詢批處理中的第一個語句。

關於我如何做到這一點的任何想法？這是sp_executesql程序的一部分。

declare @DB VARCHAR(50)
declare @SQL NVARCHAR(max)
set @DB = '[dbname]'

SET @SQL =  N'USE ' + @DB + N' CREATE VIEW vNonhiddenCategories 
AS
SELECT categories.categoryid
FROM categories
--WHERE ... irrelevant remainder of view code ...
'
execute sp_executesql @SQL

這將需要真正享受轉義和重新轉義單引號的樂趣，但我認為您所追求的技術是：

DECLARE 
 @DB  NVARCHAR(255) = QUOTENAME(N'dbname'),
 @SQL NVARCHAR(MAX);

SET @SQL =  N'EXEC ' + @DB + '.sys.sp_executesql '
+ 'N''CREATE VIEW dbo.vWhatever
AS
 SELECT x = 1, y = ''''x'''', z = GETDATE();''';

EXEC sys.sp_executesql @SQL;

所以你有點嵌套動態SQL；內部的確保 SQL 在目標數據庫上執行，而不是在本地執行。

自從這個原始問題出現以來，我採用的另一種方式：

DECLARE 
 @DB  NVARCHAR(255) = QUOTENAME('dbname'),
 @SQL NVARCHAR(MAX),
 @DBExec NVARCHAR(MAX);

SET @DBExec = @DB + N'.sys.sp_executesql';

SET @SQL = N'CREATE VIEW dbo.whatever
 AS
   SELECT x = 1, y = ''x'', z = GETDATE();';

EXEC @DBExec @SQL;

這稍微整潔一些，因為您不必雙嵌套單引號。

這是一個稍微整潔的方法，無需游標（好吧，沒有設置游標的所有腳手架）。請注意，內部動態 SQL 將僅在以下數據庫中執行：(a) 類別表存在 (b) 此視圖不存在並且 (c) 它不是系統數據庫（嗯，不是主系統數據庫之一，反正）。

DECLARE @SQL NVARCHAR(MAX) = N'';

SELECT @SQL += NCHAR(13) + NCHAR(10)    
 + N'IF NOT EXISTS (SELECT 1 FROM ' + QUOTENAME(name) 
 + '.sys.views WHERE name = ''vNonhiddenCategories'')
 AND EXISTS (SELECT 1 FROM ' + QUOTENAME(name)
 + '.sys.tables WHERE name = ''categories'')
 BEGIN
   EXEC ' + QUOTENAME(name) + '.sys.sp_executesql N''
   CREATE VIEW dbo.vNonhiddenCategories3
   AS
     SELECT x = 1, y = ''''x'''';''
 END'
 FROM sys.databases 
 WHERE database_id BETWEEN 5 AND 32766;

PRINT @sql;
-- EXEC sp_executesql @sql;

當然，“更整潔”在旁觀者的眼中。

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