Return a row from all tables and all databases?

Question

How would be the SQL SELECT statement for returning a row from all data bases on the server, but just from a specific table and some of the columns of the table.

Or, in pseudo code, something like this:

for each(database)
{
  return database.column.row;
}

You could use a cursor to loop through all the databases in the sys.databases views. And then dynamically build the query using the name column in sys.databases. — Drew Leffelman, Jun 14 '13 at 16:09
@user1948904 , yeah, while I was searching for this topic, I run into that. But I'm afraid my knowledge in SQL Queries is limited... — GeorgeCross, Jun 14 '13 at 16:13
Similar http://stackoverflow.com/questions/2875768/how-do-i-list-all-tables-in-all-databases-in-sql-server-in-a-single-result-set — PiLHA, Jun 14 '13 at 16:23

score 0 · Accepted Answer · answered Jun 14 '13 at 16:21

This was not tested, but it should be enough to get you started.

DECLARE @name as NVARCHAR(128);
DECLARE @sql AS NVARCHAR(max);

DECLARE c_Cursor CURSOR FOR
SELECT databases.name
  FROM sys.databases
 WHERE databases.database_id > 4
 ORDER BY databases.name;

OPEN c_Cursor;

FETCH NEXT FROM c_Cursor
 INTO @name;

WHILE @@FETCH_STATUS = 0
BEGIN

    SET @sql = N'SELECT table.* FROM ' + QUOTENAME(@name) + N'.dbo.table';

    EXEC @sql

    FETCH NEXT FROM c_Cursor
     INTO @name;

END;

CLOSE c_Cursor;
DEALLOCATE c_Cursor;

Return a row from all tables and all databases?

1 Answers1