Executing SQL Query on Multiple Databases

Executing SQL query on multiple databases

ApexSQL Propagate is the tool which can help in this situation. It is used for executing single or multiple scripts on multiple databases, even multiple servers. What you should do is simply select that script, then select all databases against which you want to execute that script:

When you load scripts and databases you should just click the “Execute” button and wait for the results:

Perform same SQL query on multiple databases

DECLARE @T TABLE
([DbName] SYSNAME,
[SettingName] VARCHAR(255),
[SettingValue] VARCHAR(255));

INSERT INTO
@T
EXEC sp_MSForEachDB
'SELECT
    ''?'',
    [SettingName],
    [SettingValue]
FROM
    [?]..[HostSettings]
WHERE
    [SettingName] = ''SMTPServer''';

SELECT * FROM @T;

Query across multiple databases on same server

It's not going to be the cleanest solution ever, but you could define a view on a "Master database" (if your individual databases are not going to stay constant) that includes the data from the individual databases, and allows you to execute queries on a single source.

For example...

CREATE VIEW vCombinedRecords AS
SELECT * FROM DB1.dbo.MyTable
UNION ALL
SELECT * FROM DB2.dbo.MyTable

Which allows you to do...

SELECT * FROM vCombinedRecords WHERE....

When your databases change, you just update the view definition to include the new tables.

How to run the same query on all the databases on an instance?

Try this one -

    SET NOCOUNT ON;
    
    IF OBJECT_ID (N'tempdb.dbo.#temp') IS NOT NULL
       DROP TABLE #temp
    
    CREATE TABLE #temp
    (
          [COUNT] INT
        , DB VARCHAR(50)
    )
    
    DECLARE @TableName NVARCHAR(50) 
    SELECT @TableName = '[dbo].[CUSTOMERS]'
    
    DECLARE @SQL NVARCHAR(MAX)
    SELECT @SQL = STUFF((
        SELECT CHAR(13) + 'SELECT ' + QUOTENAME(name, '''') + ', COUNT(1) FROM ' + QUOTENAME(name) + '.' + QUOTENAME(@TableName)
        FROM sys.databases 
        WHERE OBJECT_ID(QUOTENAME(name) + '.' + QUOTENAME(@TableName)) IS NOT NULL
        FOR XML PATH(''), TYPE).value('text()[1]', 'NVARCHAR(MAX)'), 1, 1, '')
    
    INSERT INTO #temp (DB, [COUNT])              
    EXEC sys.sp_executesql @SQL
    
    SELECT * 
    FROM #temp t

Output (for example, in AdventureWorks) -

COUNT       DB
----------- --------------------------------------------------
19972       AdventureWorks2008R2
19975       AdventureWorks2012
19472       AdventureWorks2008R2_Live

Running the same query on multiple databases in one query (MySQL)

SQL Server and other RDBMS products allow you do scripting in the console. You can use anything you can use in stored procedures. MySQL is unfortunately much more limited and does not allow flow control constructs outside of stored procedures and functions. That means no loops, no if-statements, no cursors. You can use variables, but only the ones that start with @.

Furthermore, if you do a loop, you'll be sending multiple result-sets back to the client. If you're just running queries from a console, this is fine. If the results are something you intend for a program to use, this may not be desirable (it may not be desirable in either case).

If you are doing this in a one-off sort of way, get a list of databases manually, and then use copy and paste to build a query using UNION ALL, like so:

SELECT FROM `first_db`.`cms_users` WHERE `email` LIKE '%admin.bilsi%'
UNION ALL
SELECT FROM `second_db`.`cms_users` WHERE `email` LIKE '%admin.bilsi%'
UNION ALL
SELECT FROM `third_db`.`cms_users` WHERE `email` LIKE '%admin.bilsi%';

If you expect the number of databases to be changing and you don't want to have to update your query, or you are sending it from a program, you can use dynamic SQL. This means building a query in a string variable and then submitting it using MySQL's prepared statement functionality.

On the console, you can use something like this (see: http://dev.mysql.com/doc/refman/5.0/en/sql-syntax-prepared-statements.html):

SELECT GROUP_CONCAT(CONCAT("SELECT FROM `", SCHEMA_NAME, "`.`cms_users` WHERE `email` LIKE '%admin.bilsi%'") SEPARATOR ' UNION ALL ')
  INTO @stmt_sql 
  FROM INFORMATION_SCHEMA.SCHEMATA 
 WHERE SCHEMA_NAME NOT IN('mysql', 'test', 'tmp', 'information_schema', 'sys', 'performance_schema');
PREPARE stmt FROM @stmt_sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

This generates the query I showed above using information from the INFORMATION_SCHEMA pseudo-database, namely, the list of databases by name (which MySQL incorrectly calls schemas). The rest is just the boilerplate code needed to prepare and execute a prepared statement, as per the linked documentation.

There are other ways, but they are even more tedious and won't buy you much.

How to loop a query in different databases in T-SQL?

I like creating a temp table for the query results, creating a table variable for my list of dbs to query, and then looping through that list of databases to execute the query for each, inserting the results into the temp table.

This way you can query the results repeatedly after running the whole loop process only once.

Try this (you'll need to modify the #results temp table declaration and the where clause of the first insert statement):

--drop temp table if it exists; this clears the results each time you run the full script
if object_id('tempdb..#results') is not null
    drop table #results
go

--recreate temp table; this is used for storing the result set.  It's created as a temp table instead of a table variable so that it can be queried repeatedly after gathering results.
create table #results (
    dbName varchar(128)
    ,cust_num varchar(128)
    , [name] varchar(128)
    , credit_hold bit
    , credit_hold_reason varchar(128)
    , [Type of credit hold reason] varchar(128)
    , credit_hold_date varchar(128)
    , credit_limit int);  --adjust column declaration as needed for query results

--create a variable to track the database list
declare @dbList table (dbName varchar(128), indx int)

insert into @dbList
select dbName = name, row_number() over (order by name) 
from master.sys.databases
--where --insert your own where clause here to narrow down which databases to run the query on.

--declare variables for use in the while loop
declare @index int = 1
declare @totalDBs int = (select count(*) from @dbList)
declare @currentDB varchar(128)
declare @cmd varchar(300)
--define the command which will be used on each database.
declare @cmdTemplate varchar(300) = '
    use {dbName};
    insert into #results
    select db_name(), cust_num, name, credit_hold, credit_hold_reason, 
       (case 
           when credit_hold_reason = ''NP'' then ''No payments''
           when credit_hold_reason = ''UK'' then ''Unknown''
           when credit_hold_reason = ''BK'' then ''Bankruptcy''
        end) as ''Type of credit hold reason'',
     credit_hold_date, credit_limit
    from [custaddr] 
    order by credit_hold_reason asc
'

--loop through each database and execute the command
while @index <= @totalDBs 
begin
    set @currentDB = (select dbName from @dbList where indx = @index)
    set @cmd = replace(@cmdTemplate, '{dbName}', @currentDB)

    execute(@cmd)

    set @index += 1
end

--see the results of the command; this can be queried repeatedly by itself after it's created the first time
select *
from #results

Note that looping through a table variable is more efficient than declaring a cursor.

SQL Query against multiple databases

My recommendation would be instead of trying to build one massive UNION ALL dynamic SQL statement, that you build a #temp table to hold the results of each output, and then it's much easier to send the same string to each database:

CREATE TABLE #hold(dbname sysname, Column1 {data type}, ...);

DECLARE @sql nvarchar(max), @exec nvarchar(1024);

SET @sql = N'SELECT DB_NAME(), *
  FROM dbo.Table1
  INNER JOIN dbo.Table3 
   ON Table3.Column6 = Table1.Column2
  AND Table3.Column3 = ''Value1''
  AND Table3.Column4 = ''Value2''
  INNER JOIN dbo.Table2 
   ON Table3.Column6 = Table2.Column2;';

DECLARE @dbname sysname, @c cursor;

SET @c = CURSOR FORWARD_ONLY STATIC READ_ONLY FOR
  SELECT name FROM sys.databases
  WHERE state = 0 -- ignores offline databases
    AND database_id > 4 -- does not include master, model, msdb, tempdb
    AND create_date > CONVERT(datetime, '20210101');

OPEN @c;

FETCH NEXT FROM @c INTO @dbname;

WHILE @@FETCH_STATUS = 0
BEGIN
  SET @exec = QUOTENAME(@dbname) + N'.sys.sp_executesql';
  INSERT #hold EXEC @exec @sql;
  FETCH NEXT FROM @c INTO @dbname;
END;

SELECT * FROM #hold;

You might also consider investing in sp_ineachdb, a procedure I wrote to help simplify running the same command in the context of each database.

• Execute a Command in the Context of Each Database in SQL Server using sp_ineachdb
• Execute a Command in the Context of Each Database in SQL Server - Part 2

Script - Query for multiple databases

That's not possible, since sp_executesql is executed as its own self-contained batch, that mean you did actually "use" other databases, but only in those batchs i mentioned earlier

I'll try to be more clear, this code of you is a batch, since there's no "GO" command inside (read my sql comments) :

DECLARE @name VARCHAR(100) -- database name
DECLARE @dothis nvarchar(200)

DECLARE db_cursor CURSOR FOR
SELECT name
FROM master.dbo.sysdatabases
WHERE name like 'kde_0%'
order by name

OPEN db_cursor  
FETCH NEXT FROM db_cursor INTO @name  

WHILE @@FETCH_STATUS = 0
BEGIN  
       set @dothis = 'use [' + @name + ']'
       -- this will create another batch and execute the @dothis
       -- it'll have nothing todo with your current executing batch, 
       -- which is calling the sp_executesql
       exec sp_executesql @dothis 

       /* Start query */
       select description from dbo.basicdata
       /* End query */

       FETCH NEXT FROM db_cursor INTO @name  
END  

CLOSE db_cursor  
DEALLOCATE db_cursor

So, there's only one way left, write whatever you want to do with the database inside the @dothis :

declare @dothis nvarchar(max)
set @dothis = '
use [' + @name + ']
-- query start
Select description from dbo.basicdata
-- query end 
'
exec sp_executesql @dothis 

---

Related Topics