September 4, 2018 at 3:01 am
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
September 4, 2018 at 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.
September 4, 2018 at 4:49 am
Jonathan AC Roberts - Tuesday, September 4, 2018 3:54 AMThere 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?
September 4, 2018 at 12:10 pm
s.stieler - Tuesday, September 4, 2018 4:49 AMJonathan AC Roberts - Tuesday, September 4, 2018 3:54 AMThere 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
BEGINSET @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)
September 4, 2018 at 6:23 pm
USE tempdbViewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply