T-SQL query to return results into single table

Question

I am running a query against all databases within an instance. There are a few hundered databses with identical schema (tables and all).

This is the query:

EXEC sp_MSforeachdb 'Use ? SELECT top 1  Column1, Column2 from [TableName]where Column3 = ''SpecificValue'' order by Column4 desc'

The query works alright and returns the results which I want, but not in a way I want them.

After I run this query, in the results pane I get one mini table per database so I end up with a few hundered mini tables. It's very impractical, and it forces me to copy results one by one.

Is there a way to rewrite this query so that it returns all results inone table with 2 columns. I would like each row to be like

value of column 1 from db 1 \ value of column2 dfrom db1

value of column 1 from db 2 \ value of column2 dfrom db2

value of column 1 from db 3 \ value of column2 dfrom db3

and so on...

Thank you Brad. It certainly could be one of the ways of doing it. — QWE, Jun 10 '16 at 14:56

score 1 · Accepted Answer · answered Jun 10 '16 at 14:40

1

You can use a global temp table for this:

CREATE TABLE ##tmpTable(DBName VARCHAR(MAX),Content VARCHAR(MAX));
EXEC sp_MSforeachdb 'Use ? INSERT INTO ##tmpTable SELECT TOP 1 ''?'', TABLE_NAME FROM INFORMATION_SCHEMA.TABLES'
SELECT * FROM ##tmpTable;
GO
DROP TABLE ##tmpTable;

answered Jun 10 '16 at 14:40

Shnugo

66,100
9
53
114

Thank you! It does exactly what I wanted. – QWE Jun 10 '16 at 14:56

square_particle · Answer 2 · 2016-06-10T18:06:17.537

Although there are cases where a global temp table, i.e. "##", may be warranted, I try to stray away from them. Instead, you could still use a single session-based temp table, i.e. "#", or a table variable and just take a dynamic SQL approach to the problem.

Here's an alternative:

Create a temp table, e.g. #tmp.
Create a single executable string of SQL to be run. This string will include a query for each database.
Then simply execute the SQL string.

Here is the code:

create table #tmp (
    database_id int,
    database_name varchar(128),
    object_id int,
    object_name varchar(128));

declare
    @Sql varchar(max) = (
        select 'insert #tmp (database_id, database_name, object_id, object_name) select ' + convert(varchar(128), database_id) + ', ''' + name + ''', object_id, name from ' + name + '.sys.objects;'
        from sys.databases
        where name in ('master', 'msdb', 'tempdb')
        for xml path(''));

exec (@Sql);

select
    database_name,
    object_count = count(*)
from #tmp
group by
    database_name
order by
    1;

Below are the results from the group by above:

database_name         object_count
--------------------- ------------
master                116
msdb                  1194
tempdb                81

T-SQL query to return results into single table

2 Answers2