Alter stored procedure across databases

Needed to alter a particular stored procedure over a few thousand databases.  Here is an example script that uses a cursor and dynamic sql to do this.  Key point here is to have the USE statement switch the database prior to issuing the CREATE OR ALTER command so that each database gets this change and since a create or alter statement for a procedure has to be the first statement.

Note you will need to double escape your quotes on the contents of the stored procedure text to get it to change appropriately.

DECLARE @database SYSNAME;
DECLARE @SQLBASE NVARCHAR(MAX) = N'
USE [DATABASENAME]
DECLARE @SQL NVARCHAR(MAX) = N''
CREATE OR ALTER PROC Hello
@textToPrint NVARCHAR(100) = ''''Mittens on Kittens''''
AS                        
BEGIN
    SET NOCOUNT ON;
	PRINT @textToPrint
END
''
EXEC sys.sp_executesql @stmt = @SQL;
';
DECLARE @SQL NVARCHAR(MAX);
DECLARE CSR_DATABASES CURSOR
	FOR 
	SELECT [name] FROM sys.databases
	WHERE [name] IN
	(
		'Zone1',
		'DbCache', 
		'test1'
	)
OPEN CSR_DATABASES;
FETCH NEXT FROM CSR_DATABASES INTO @database;
WHILE ( @@FETCH_STATUS = 0 ) 
BEGIN
	RAISERROR ('Working on database: %s', 10, 1, @database);
	SET @SQL = REPLACE(@SQLBASE, 'DATABASENAME', @database);
	PRINT @SQL;
	EXEC sys.sp_executesql @SQL
	FETCH NEXT FROM CSR_DATABASES INTO @database;
END
CLOSE CSR_DATABASES;
DEALLOCATE CSR_DATABASES;

And the output is

Working on database: Zone1

USE [Zone1]
DECLARE @SQL NVARCHAR(MAX) = N'
CREATE OR ALTER PROC Hello
@textToPrint NVARCHAR(100) = ''Mittens on Kittens''
AS                        
BEGIN
    SET NOCOUNT ON;
	PRINT @textToPrint
END
'
EXEC sys.sp_executesql @stmt = @SQL;

Working on database: DbCache

USE [DbCache]
DECLARE @SQL NVARCHAR(MAX) = N'
CREATE OR ALTER PROC Hello
@textToPrint NVARCHAR(100) = ''Mittens on Kittens''
AS                        
BEGIN
    SET NOCOUNT ON;
	PRINT @textToPrint
END
'
EXEC sys.sp_executesql @stmt = @SQL;

Working on database: test1

USE [test1]
DECLARE @SQL NVARCHAR(MAX) = N'
CREATE OR ALTER PROC Hello
@textToPrint NVARCHAR(100) = ''Mittens on Kittens''
AS                        
BEGIN
    SET NOCOUNT ON;
	PRINT @textToPrint
END
'
EXEC sys.sp_executesql @stmt = @SQL;


Completion time: 2021-07-22T21:04:58.4126017-05:00

To me this is a nice way to show how cursors and dynamic sql can do some powerful steps.