Running Query in multiple Databases without use of Cursors

There are times when you need to loop through all the databases or database objects to perform some tasks.For example you want to run a DBCC command against all the databases or take backups of all the databases on the server or you want to rebuild all the indexes of all the tables in the databases or you want to know the size of each table in a database. The simplest approach would be to create a cursor and loop through it, which requires you to write several lines of code. Is there any way to simplify the coding efforts for these kind of works?

SQL Server has couple of undocumented system stored procedures, in the master database, which allow you to loop through all/selected databases or loop through all/selected user tables or views, stored procedures by following system stored procedures

1. sp_MSforeachdb system stored procedure ==> Loop through multiple databases
2. sp_MSforeachtable system stored procedure ==> Loop through multiple tables
3. sp_MSforeach_worker stored procedure ==> loop through multiple views, stored procedures etc.

Let us discuss one by one for above mentioned procedures.

1 .sp_MSforeachdb

Syntax for the sp_MSforeachdb is given as below

EXEC sp_MSforeachdb @command 

Where @command is a variable-length string. Let us take an example: suppose you want to search for a table that starts with "EMP" in all the databases on the current instance of the SQL Server , so we can use the below query

DECLARE @command varchar(1000)
SELECT @command = 'USE ? SELECT name FROM sys.tables WHERE type = ''U'' 
AND NAME LIKE ''SH%'' ORDER BY name'
EXEC sp_MSforeachdb @command 

The above set of query will iterate through all the databases in the current instance of the sql server and will list the matching records. Below result is from one of the my SQL Server Instance. In case you dont want to include some set of the databases so you can easily skip them from the query. See below example

DECLARE @command varchar(1000)
SELECT @command = 'IF ''?'' NOT IN(''master'',''model'',''msdb'',''tempdb'') 
     BEGIN 
      USE ? 
       SELECT name FROM sys.tables WHERE type = ''U'' 
       AND NAME LIKE ''EMP%'' ORDER BY name 
     END'
EXEC sp_MSforeachdb @command 

The result from the above query will be same and it will not search for databases which are excluded in the query. Similarly you can create a stored procedure in all the database using the mentioned procedure. Let us take an simple example in which we are creating stored procedure which will return the name of the all employees having salary greater than 5000 from the Employee table. It is mandatory that the tables which are being used in the procedure should exists in all the databases which are targeted for creating the procedure. Alternatively we can add check if table doesn't exists in the database then it will not create the procedure, I am not going to handle these conditions in the example given, I am considering all the databases have the table Employee.

DECLARE @command varchar(1000)
SELECT @command = 'IF ''?'' NOT IN(''master'', ''model'', ''msdb'', ''tempdb'') 
BEGIN USE ? 
EXEC(''CREATE PROCEDURE P_GetEmployee AS SELECT EmpName,EmpSalary FROM Employee
WHERE EmpSalary>5000 ORDER BY EmpSalary DESC'') 
END'
EXEC sp_MSforeachdb @command 

Note : You can also specify the database for which you want to run the query. Remove NOT keyword from the above query and it will run for the databases specified in the query.

2. sp_MSforeachtable

Updating Soon

3. sp_MSforeach_worker

Updating Soon

Reference : http://www.mssqltips.com