SQL – Select from Multiple databases

I have 10 databases all in the same instance and schema with identical tables.

Looking to create a SELECT query which can use a variable of the database names and return all of the records into a single dataset.

I’ve been looking around and put the following together as a test (the real select statement is much larger with multiple joins)

This test does work but it returns two result sets one for each database.

Is there a way to combine the results into one set or am I using the wrong approach?

Thanks in advance

DECLARE @DB_NAME VARCHAR(6);

DECLARE CURSOR_ALLDB_NAMES CURSOR FOR 
    SELECT name
    FROM sys.databases
    WHERE name IN ('CN2DAT', 'AU1DAT') 

OPEN CURSOR_ALLDB_NAMES

FETCH NEXT FROM CURSOR_ALLDB_NAMES INTO @DB_NAME

WHILE @@Fetch_Status = 0
BEGIN
    EXEC ('SELECT * FROM ' + @DB_NAME + '.dbo.ICITEM')

    -- EXEC ('USE '+ @DB_NAME + ' SELECT * from dbo.ICITEM')

    FETCH NEXT FROM CURSOR_ALLDB_NAMES INTO @DB_NAME
END

CLOSE CURSOR_ALLDB_NAMES
DEALLOCATE CURSOR_ALLDB_NAMES

Answer

I’d create a view which combines the select statements. e.g.

CREATE VIEW v_ICITEM
AS
SELECT * FROM CN2DAT.dbo.ICITEM
UNION ALL 
SELECT * FROM AU1DAT.dbo.ICITEM
go;

You could include the source database as a column also:

CREATE VIEW v_ICITEM
AS
SELECT 'CN2DAT' AS Db, * FROM CN2DAT.dbo.ICITEM
UNION ALL 
SELECT 'AU1DAT', * FROM AU1DAT.dbo.ICITEM
go;