How to Run Stored Procedure Against Multiple Databases in SQL Server - SQL Server / TSQL Scripts

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