CODEWITHSUNDEEP
sqlsql-servert-sql
Dan
Dan

Reputation: 1192

Union of multiple sp_MSforeachdb result sets

I can successfully query the same table in multiple databases as follows:

DECLARE @command varchar(1000)
SELECT @command = 'select * from table'
EXEC sp_MSforeachdb @command

However, all of these results are, as expected, returned in different result windows. What's the easiest way to perform a union of all of these results?

Upvotes: 1

Views: 7180

Answers (3)

Aaron Bertrand
Aaron Bertrand

Reputation: 280351

Please stop using sp_MSforeachdb. For anything. Seriously. It's undocumented, unsupported, and spectacularly broken:

If you know that all databases have the same table (and that they all have the same structure!), you can do this:

DECLARE @sql NVARCHAR(MAX);

SET @sql = N'';

SELECT @sql = @sql + N'UNION ALL SELECT col1,col2 /*, etc. */
  FROM ' + QUOTENAME(name) + '.dbo.tablename'
FROM sys.databases WHERE database_id > 4 AND state = 0;

SET @sql = STUFF(@sql, 1, 10, '');

EXEC sp_executesql @sql;

This ignores system databases and doesn't attempt to access any databases that are currently not ONLINE.

Now, you may want to filter this further, e.g. not include any databases that don't have a table called tablename. You'll need to nest dynamic SQL in this case, e.g.:

DECLARE @sql NVARCHAR(MAX);

SET @sql = N'DECLARE @cmd NVARCHAR(MAX);

SET @cmd = N'''';';

SELECT @sql = @sql + N'

SELECT @cmd = @cmd + N''UNION ALL
SELECT col1,col2 /*, etc. */ FROM ' 
  + QUOTENAME(name) + '.dbo.tablename ''
WHERE EXISTS (SELECT 1 FROM ' + QUOTENAME(name) 
 + '.sys.tables AS t
 INNER JOIN ' + QUOTENAME(name) + '.sys.schemas AS s
 ON t.[schema_id] = s.[schema_id]
 WHERE t.name = N''tablename''
 AND s.name = N''dbo'');'
FROM sys.databases WHERE database_id > 4 AND state = 0;

SET @sql = @sql + N';

SET @cmd = STUFF(@cmd, 1, 10, '''');

PRINT @cmd;
--EXEC sp_executesql @cmd;';

PRINT @sql;
EXEC sp_executesql @sql;

This doesn't validate the column structure is compatible, but you'll find that out pretty quickly.

Upvotes: 9

that_roy
that_roy

Reputation: 104

Had some collation issues and had to use

AND COLLATION_NAME = 'SQL_Latin1_General_CP1_CI_AS'

Upvotes: 1

gvee
gvee

Reputation: 17161

Another way to skin this cat is to use dynamic SQL:

DECLARE @sql varchar(max);

SELECT @sql = Coalesce(@sql + ' UNION ALL ', '') + 'SELECT list, of, columns FROM ' + QuoteName(name) + '.schema.table'
FROM   sys.databases
;

PRINT @sql
--EXEC (@sql);

Upvotes: 3

Related Questions