Creating a View using a query created by sp_executesql

Question

I posted a question about creating a dynamic sql statement and got the following code which fulfills this task:

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

Now executing this code displays the table which I want to store for later use. I was told that I should use a view that stores these results but I dont know how to do that. Can I create a view that is populated by this dynamic sql statements and sotre it for repeated use? Thank you all.

Answer 1

So - expanding on your original script, the following checks to see if the view already exists (and if it does, checks to see if the view definition will be changing). There isn't much sense in dropping the view and recreating it if it isn't really changing. If the view already exists and the new view will be different, then the current view is dropped.

The script may need a little 'clean-up'

/*
--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