使用 sp_executesql 創建的查詢創建視圖

我發布了一個關於創建動態 sql 語句的問題，並獲得了以下程式碼來完成此任務：

DECLARE @TableName nvarchar(400)
DECLARE @DynSQL nvarchar(MAX)
Set @DynSQL = ''

DECLARE cursor1 CURSOR FOR 
   select name
   from sys.tables
   where name like 'DDOS_WAF_ACCOUNT_%'

OPEN cursor1

FETCH NEXT FROM cursor1 INTO @TableName

WHILE @@FETCH_STATUS = 0
BEGIN
   -- Add the select code.
   Set @DynSQL = @DynSQL + 'Select * from ' + @TableName
   FETCH NEXT FROM cursor1
   INTO @TableName

   -- If the loop continues, add the UNION ALL statement.
   If @@FETCH_STATUS = 0
   BEGIN
       Set @DynSQL = @DynSQL + ' UNION ALL '
   END

END

CLOSE cursor1
DEALLOCATE cursor1

Print @DynSQL
exec sp_executesql @DynSQL

現在執行此程式碼會顯示我要儲存以供以後使用的表。有人告訴我應該使用儲存這些結果的視圖，但我不知道該怎麼做。我可以創建一個由這個動態 sql 語句填充的視圖並將其重新用於重複使用嗎？謝謝你們。

所以 - 擴展你的原始腳本，下面檢查視圖是否已經存在（如果存在，檢查視圖定義是否會改變）。如果視圖沒有真正改變，那麼刪除視圖並重新創建它沒有多大意義。如果視圖已經存在並且新視圖將不同，則刪除目前視圖。

腳本可能需要一點“清理”

/*
--initially create 2 tables and run the script code
CREATE TABLE dbo.xx_table01 (col1 int)
CREATE TABLE dbo.xx_table02 (col1 int)

--add another table and run the script code
CREATE TABLE dbo.xx_table03 (col1 int)

--add another table and run the script code
CREATE TABLE dbo.xx_table04 (col1 int)

--add another table and run the script code
CREATE TABLE dbo.xx_table05 (col1 int)
*/
--
DECLARE @CurrentView nvarchar(MAX) = null
DECLARE @SchemaName nvarchar(400)
DECLARE @TableName nvarchar(400)
DECLARE @DynSQL nvarchar(MAX)
DECLARE @DynDROP nvarchar(MAX) = 'DROP VIEW XX_VIEW'
SET NOCOUNT ON
Set @DynSQL = 'CREATE VIEW XX_VIEW AS '

set @CurrentView = (select VIEW_DEFINITION from INFORMATION_SCHEMA.VIEWS WHERE TABLE_SCHEMA='dbo' and TABLE_NAME='XX_VIEW')

DECLARE cursor1 CURSOR FOR 
   select TABLE_SCHEMA,TABLE_NAME
   from INFORMATION_SCHEMA.TABLES
   where 
   TABLE_SCHEMA='dbo' AND
   TABLE_NAME like 'xx_table%'

OPEN cursor1

FETCH NEXT FROM cursor1 INTO @SchemaName, @TableName

WHILE @@FETCH_STATUS = 0
BEGIN
   -- Add the select code.
   Set @DynSQL = @DynSQL + 'Select * from ' + @SchemaName +'.' + @TableName
   FETCH NEXT FROM cursor1
   INTO @SchemaName, @TableName

   -- If the loop continues, add the UNION ALL statement.
   If @@FETCH_STATUS = 0
   BEGIN
       Set @DynSQL = @DynSQL + ' UNION ALL '
   END

END

CLOSE cursor1
DEALLOCATE cursor1

IF @CurrentView = @DynSQL 
   PRINT 'VIEW IS THE SAME, NEW VIEW WASN''T CREATED'
ELSE
   BEGIN
       if @CurrentView is not null
       BEGIN
       print @DynDROP
           exec sp_executesql @DynDROP
       END
       PRINT @DynSQL
       exec sp_executesql @DynSQL
   END

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