SQL Server: Tips and Tricks - 2 (2017)

How to Run Stored Procedure against Multiple Databases in SQL Server

Scenario:

You are working as SQL Server developer. The company you are working create a new database for each of the client with same objects such as tables/Stored Procedures. You have one stored procedure with the same name let's say dbo.LoadCustomer present in all the databases. You need to execute that Stored Procedure from multiple databases. The script can be used one time or you can also run by using SQL Server agent if has to run on schedule.

Solution:

We can use Cursor to loop through list of the databases in which our Stored Procedure is present and we want to execute. Below is sample script. Please change the @SPName and filter the list of database as per your requirements.

--Use Cursor to Loop through Databases

DECLARE @DatabaseName AS varchar(500)

--Provide the name of SP that you want to run

DECLARE @SPName AS varchar(128) = 'dbo.loadCustomer'

DECLARE DBCursor CURSOR FOR

--Filter the list of the database in which Stored Procedure exists

SELECT

NAME

FROM sys.databases

WHERE database_id > 4

OPEN DBCursor

FETCH NEXT

FROM DBCursor

INTO @DatabaseName

WHILE @@FETCH_STATUS = 0

BEGIN

DECLARE @DBName AS nvarchar(500);

SET @DBName = QUOTENAME(N'' + @DatabaseName + '');

--USE Dynamic SQL to change the Database Name and

--Execute Stored Procedure from that Database

EXEC (

N'USE ' + @DBName + N'; EXEC(''' + @SPName + '

'');'

);

FETCH NEXT

FROM DBCursor

INTO @DatabaseName

END

CLOSE DBCursor

DEALLOCATE DBCursor