How do I create a long query (about 500 DBs) and execute it?

Question

How do I create a long query (about 500 DBs) and execute it?

s.stieler

SSC Rookie

Points: 36
More actions
September 4, 2018 at 3:01 am

#376847

Hello everybody,
I have a problem that I can not get on.
Every day, I have to collect a lot of data and merge it for reporting.
I have about 500 databases (for clients) which are all built exactly the same.
I need data from all databases and must create a select ... union all and execute it afterwards.
The query looks something like this:
SELECT
   year (duedate)
   month (duedate)
   @Database,
   account,
   Sum (debit),
   Sum (Credit)
FROM @Database
GROUP BY
   year (duedate),
   month (duedate),
   account
Unfortunately, I do not know when new databases are added or disappear.
Therefore, I would have to get the databases from the sys.databases.
Finally, build a loop that connects all 500 Select statements with a union all and then execute them.
Can someone help me how to write something like that?
Best regards
Simon

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Jonathan AC Roberts SSCoach Points: 18263 More actions · Answer 1

There is the undocumented (by Microsoft) command sp_MSforeachdb
You can also create a server group. If you run a query from a server group it will run it on all the databases in the group.
If you want a UNION (i.e. all the rows back in one resultset) it might be better to create some dynamic SQL to create a large SQL statement by having a cursor selecting from sysdatabases.
You should really also select DB_NAME() in your query so you can tell which database the query was run on.

s.stieler SSC Rookie Points: 36 More actions · Answer 2

Jonathan AC Roberts - Tuesday, September 4, 2018 3:54 AM
There is the undocumented (by Microsoft) command sp_MSforeachdb
You can also create a server group. If you run a query from a server group it will run it on all the databases in the group.
If you want a UNION (i.e. all the rows back in one resultset) it might be better to create some dynamic SQL to create a large SQL statement by having a cursor selecting from sysdatabases.
You should really also select DB_NAME() in your query so you can tell which database the query was run on.

Thank you very much.
I'll take a look at the functions.

Before, I tried this:
DECLARE @databases TABLE (dbname NVARCHAR (200));
DECLARE @Database NVARCHAR (4000);
DECLARE @DatabaseCmd NVARCHAR (4000);
DECLARE @sql nvarchar (max);

INSERT INTO @databases
     (Dbname)
SELECT
     '[' + DD.name + ']'
FROM
       sys. [databases] AS DD
WHERE
         DD.name like '% xxx%'

SELECT
     @Database = MIN (D.dbname),
     @DatabaseCmd = @Database + '.sys.sp_ExecuteSQL'
FROM
     @databases AS D;

WHILE @Database IS NOT NULL
BEGIN

SET @sql = @sql + 'SELECT Statement'

INSERT INTO HELP_DB
(SAVE_INT, society, dbstatement)
VALUES (@Database, @SQL);

- EXEC @DatabaseCmd @sql;

     DELETE
         @databases
     WHERE
         dbname = @Database;

     SELECT
         @Database = MIN (D.dbname),
         @DatabaseCmd = @Database + '.sys.sp_ExecuteSQL'
     FROM
         @databases D;

END

But how do I execute the result from HELP_DB?

Or do you have another solution?

Jonathan AC Roberts SSCoach Points: 18263 More actions · Answer 3

s.stieler - Tuesday, September 4, 2018 4:49 AM
Jonathan AC Roberts - Tuesday, September 4, 2018 3:54 AM
There is the undocumented (by Microsoft) command sp_MSforeachdb
You can also create a server group. If you run a query from a server group it will run it on all the databases in the group.
If you want a UNION (i.e. all the rows back in one resultset) it might be better to create some dynamic SQL to create a large SQL statement by having a cursor selecting from sysdatabases.
You should really also select DB_NAME() in your query so you can tell which database the query was run on.
Thank you very much.
I'll take a look at the functions.
Before, I tried this:
DECLARE @databases TABLE (dbname NVARCHAR (200));
DECLARE @Database NVARCHAR (4000);
DECLARE @DatabaseCmd NVARCHAR (4000);
DECLARE @sql nvarchar (max);
INSERT INTO @databases
     (Dbname)
SELECT
     '[' + DD.name + ']'
FROM
       sys. [databases] AS DD
WHERE
         DD.name like '% xxx%'
SELECT
     @Database = MIN (D.dbname),
     @DatabaseCmd = @Database + '.sys.sp_ExecuteSQL'
FROM
     @databases AS D;
WHILE @Database IS NOT NULL
BEGIN
SET @sql = @sql + 'SELECT Statement'
INSERT INTO HELP_DB
(SAVE_INT, society, dbstatement)
VALUES (@Database, @SQL);
- EXEC @DatabaseCmd @sql;
     DELETE
         @databases
     WHERE
         dbname = @Database;
     SELECT
         @Database = MIN (D.dbname),
         @DatabaseCmd = @Database + '.sys.sp_ExecuteSQL'
     FROM
         @databases D;
END
But how do I execute the result from HELP_DB?
Or do you have another solution?

I was thinking something like this:
DECLARE @myCursor cursor, @DbName nvarchar(128) DECLARE @NewLine nvarchar(MAX) = CHAR(13)+ CHAR(10) DECLARE @sSql1 as nvarchar(MAX)='SELECT [year (duedate)],[month (duedate)], DB_NAME() DBName,SUM(debit), SUM(Credit), account FROM ' DECLARE @sSql2 as nvarchar(MAX)='.dbo.myTable GROUP BY [year (duedate)],[month (duedate)],[account]' DECLARE @AllSql as nvarchar(MAX)=''

SET @myCursor = cursor FOR SELECT name FROM dbo.sysdatabases WHERE NAME NOT IN ('msdb','model','tempdb','master') ORDER BY name OPEN @myCursor FETCH NEXT FROM @myCursor INTO @DbName WHILE @@FETCH_STATUS=0 BEGIN

SET @AllSql = @AllSql + @sSql1 + QUOTENAME(@DbName) + @sSql2

FETCH NEXT FROM @myCursor INTO @DbName IF @@FETCH_STATUS=0 SET @AllSql = @AllSql + ' UNION ALL' + @NewLine

END CLOSE @myCursor DEALLOCATE @myCursor PRINT @AllSql --EXEC(@AllSql)

Joe Torre SSChampion Points: 10248 More actions · Answer 4

You should be able to adapt this method to your use case:

USE tempdb
GO
DROP TABLE IF EXISTS tempdb.dbo.temp
GO
CREATE TABLE tempdb.dbo.temp (DBName sysname NULL, schema_name sysname NULL, TableName sysname NULL);
GO
DECLARE @sql nvarchar(MAX)=N'';
DECLARE @offset int=0, @fetch int = 1;
WHILE @offset<(SELECT Count(*) FROM sys.databases)-1
BEGIN
SELECT @sql+='INSERT tempdb.dbo.temp
SELECT '''+name+''', schema_name(t.schema_id), t.name
FROM '+QuoteName(name)+'.sys.tables t
LEFT OUTER JOIN tempdb.dbo.temp tdb
ON t.name = tdb.TableName and tdb.DBName = '''+name+'''
WHERE TableName IS NULL AND tdb.DBName IS NULL;'
FROM sys.databases
WHERE name NOT IN ('master', 'msdb', 'model', 'tempdb')
ORDER BY name
OFFSET @offset ROWS FETCH NEXT @fetch ROWS ONLY;
SET @offset += @fetch;
PRINT(@sql);
EXEC sys.sp_executesql @sql;
SET @sql = N'';
END
SELECT DBName
, TableName
FROM tempdb.dbo.temp;