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.