Select same table name from all databases in SQL Server

Question

I have this dynamic query, that is union from all my databases (that is start with "Db") the same table ("Tbl_SameTable").

DECLARE @tableName nvarchar(256) = 'Tbl_SameTable'
DECLARE @sql nvarchar(max) = ''

SELECT @sql = @sql + CASE WHEN @sql <> '' THEN 'UNION ALL ' ELSE '' END 
                   + 'SELECT * FROM [' + dbs.name + ']..[' + @tableName + '] '
FROM sys.sysdatabases dbs
WHERE left(dbs.name,2) = 'Db'

EXEC(@sql)

I want to add two things to this query:

1. Add a column of database name
2. Assign the query result to a "temp table" or "table variable"

I do not know if this is important but, the "Tbl_SameTable" is a 5 column table (int, nvarchar, int,nvarchar,nvarchar)

Answer 1

This is untested, however, you'll want something like this. As this is pseudo SQL, you'll need to replace {Columns} with the actual names (not *) for it to work. For the CREATE TABLE you'll need to define the data type of said columns too.

DECLARE @SchemaName sysname = N'dbo',
        @TableName sysname = N'YourTable';

CREATE TABLE #Temp (DatabaseName sysname,
                    {Columns});

DECLARE @SQL nvarchar(MAX),
        @CRLF nchar(2) = NCHAR(13) + NCHAR(10);

SET @SQL = STUFF((SELECT @CRLF + N'UNION ALL' + @CRLF +
                         N'SELECT N' + QUOTENAME(d.[name],'''') + N' AS DatabaseName, {Columns}' + @CRLF +
                         N'FROM ' + QUOTENAME(d.[name]) + N'.' + QUOTENAME(@SchemaName) + N'.' + QUOTENAME(@TableName)
                  FROM sys.databases d
                  WHERE d.[name] LIKE 'Db%'
                  ORDER BY database_id
                  FOR XML PATH(N''),TYPE).value('(./text())[1]','nvarchar(MAX)'),1,13, N'') + N';'

--PRINT @SQL; --Your Best Friend

INSERT INTO #Temp(DatabaseName, {Columns})
EXEC sys.sp_executesql @SQL;

And, of course, if it doesn't work your best friend will be there to help you out.

Answer 2

How about using sp_MSforeachdb and adding the results to another table?

DROP TABLE IF EXISTS #tmp
CREATE TABLE #tmp (col1 INT, col2 INT,...)

DECLARE @command varchar(1000) 
SELECT @command = 'IF ''?'' LIKE ''Db%'' BEGIN USE ? 
   EXEC(''INSERT INTO #tmp (col1, col2,...) SELECT col1, col2,... from Tbl_SameTable'') END' 
EXEC sp_MSforeachdb @command 

SELECT * FROM #tmp