Create view that selects from identical tables from all databases on the server and can handle any time a new database is added to the server

Question

I have a system that takes in Revit models and loads all the data in the model to a 2016 SQL Server. Unfortunately, the way the system works it created a new database for each model that is loaded. All the databases start with an identical schema because there is a template database that the system uses to build any new ones.

I need to build a view that can query data from all databases on the server but can automatically add new databases as they are created. The table names and associated columns will be identical across all databases, including data types.

Is there a way to pull a list of current database names using:

SELECT [name] FROM sys.databases

and then use the results to UNION the results from a basic SELECT query like this:

SELECT 
    [col1]
    ,[col2]
    ,[col3]
FROM [database].[dbo].[table]

Somehow replace the [database] part with the results of the sys.databases query?

The goal would be for the results to look as if I did this:

SELECT 
    [col1]
    ,[col2]
    ,[col3]   
FROM [database1].[dbo].[table]
UNION
SELECT 
    [col1]
    ,[col2]
    ,[col3]    
FROM [database2].[dbo].[table]

but dynamically for all databases on the server and without future management from me.

Thanks in advance for the assistance!

***Added Info: A couple suggestions using STRING_AGG have been made, but that function is not available in 2016.

How would you approach it with a stored procedure? Could the procedure alter an existing view and I could schedule the procedure to run daily? — davidjay43, Aug 06 '21 at 20:34
Why not adjust the process that creates these tables by adding the view creation / alteration at the same time? Alternatively, you could certainly schedule the view creation on some periodic basis. — SMor, Aug 06 '21 at 22:51
That's ultimately what I'm aiming to do. I'm just unclear on the approach I should take to build the view dynamically grabbing the tables/columns from the available DB's. — davidjay43, Aug 07 '21 at 02:16
You might want to research [DDL triggers](https://learn.microsoft.com/en-us/sql/relational-databases/triggers/ddl-triggers?view=sql-server-ver15). They provide a way to execute code in response to events like `CREATE_DATABASE`. — HABO, Aug 07 '21 at 03:26
Does this answer your question? [Displaying multiple database tables in one table](https://stackoverflow.com/questions/65511529/displaying-multiple-database-tables-in-one-table) — Charlieface, Aug 07 '21 at 23:51

JOakland2021 · Accepted Answer · 2021-08-07T22:54:41.263

Try this. It will automatically detect and include new databases with the specified table name. If a database is dropped it will automatically exclude it.

I updated the TSQL. STRING_AGG concatenates the string with each database. Without it it only returns the last database. STRING_AGG is more secure than += which also concatenates. I changed the code so it generates and executes the query. In SQL 2019 the query is all in one line using +=. I don't have SQL 2016. It may format it better in SQL 2016. You can uncomment --SELECT @SQL3 to see what the query looks like. Please mark as answer if this is what you need.

DECLARE @TblName TABLE
(
  TblName VARCHAR(100)
)

Declare @SQL VARCHAR(MAX),
@SQL3 VARCHAR(MAX),
@DBName VARCHAR(50),
@Count Int, 
@LoopCount Int 
Declare @SQL2 VARCHAR(MAX) = ''

Select Identity(int,1,1) ID, name AS DBName  into #Temp from sys.databases 
Select @Count = @@RowCount 
Set @LoopCount = 1 

While @LoopCount <= @Count 
Begin 
    SET @DBName = (SELECT DBName FROM #Temp Where ID = @LoopCount)
    SET @SQL =
    ' USE ' + @DBName + 
    ' SELECT TABLE_CATALOG FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = ''table'''

    INSERT INTO @TblName (TblName)
    EXEC (@SQL)

    Set @LoopCount=@LoopCount + 1 
End 

SELECT @SQL2 +=
' SELECT ' + char(10) + 
'    [col1] ' + char(10) + 
'    ,[col2] ' + char(10) + 
'    ,[col3] ' + char(10) + 
' FROM [' + TblName + '].[dbo].[table] ' + char(10) + 
' UNION '
FROM @TblName

DROP TABLE #Temp

SET @SQL3 = (SELECT SUBSTRING(@SQL2, 1, LEN(@SQL2) - 5))
--SELECT @SQL3
EXEC (@SQL3)

I should have mentioned that I was on Sql Server 2016. Sorry. I edited the post to mention this. STRING_AGG isn't available to me. Any work-around for this? — davidjay43, Aug 07 '21 at 02:14
I should have mentioned that I was on Sql Server 2016. Sorry. I edited the post to mention this. STRING_AGG isn't available to me. If I'm reading the code correctly it looks like you're using that to just lay the code out in a more readable format. I moved the string that @SQL2 was getting set to into a single line. The results are just a single returned value of SLQ2 was set to and it is just for one of the databases. I moved the END from before SELECT SQL2 to just before the DROP TABLE and I now get a result for each database, but again just the SQL2 string. — davidjay43, Aug 07 '21 at 02:45
This did the trick. Thanks! This process will never be part of a public facing application. Any additional security steps that I should implement due to the += ? — davidjay43, Aug 08 '21 at 12:34

Create view that selects from identical tables from all databases on the server and can handle any time a new database is added to the server

1 Answers1