Create a view that dynamically UNIONs new tables

Question

I have a database named DDOS_WAF_REPORT that has multiple tables with the name format dbo.DDOS_WAF_ACCOUNT_###### (a 5 or 6 digit ID number).

All the tables have the exact same structure (both column names and types). I want to create a view to UNION ALL them together. In addition there will be new tables of the same type that will be added later into the database, so I need the view to be able to 'run through' all existing tables in the database and add them (either adding or creating the view from scratch).

I've created a simple view with the tables name's hard coded but I'm having difficulty how to approach the issue of having the view created dynamically. I'm using SQL Server 2012.

The tables being added are the result of an outside script running against an API per account so I can't avoid having multiple tables in the database. I assumed I'll probably have to write some script code that would actually run and check for new table names but I wanted to see some example to figure out how to do that.

Answer 1

You can prepare the SQL statement using a cursor to build the SQL string, and execute it using sp_executesql.

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

You can use text mode and see the query that it is building, thanks to the print statement near the end.