SQL Server: Tips and Tricks - 2 (2017)
How to Create Same Stored Procedure on All Databases in SQL Server
We often face this situation where we need to create a same Stored Procedure in multiple databases. This script can be really usefully when you are working as SQL Server DBA and you have to deploy same Stored Procedure to all databases or multiple databases Or you are working as developer and your team asked you to prepare script which will create the Stored Procedure in all databases in SQL Server.
I used cursor in below script to loop through the databases. You can always change your Where clause to get the list of databases on which you would like to create Stored Procedure.
It is always great idea to test your script in DEV, QA and UAT environment before deploying/ running on Production environment.
--Provide the DDL Statement that you would like to run for Create Stored Procedure in Each Database in SQL Server
-- Notice that if you have string in your SP, then you have to have 4 single quote around it.
DECLARE @DDL VARCHAR(MAX)
'Create Procedure dbo.SP_Test
--My all statements in SP
DECLARE @DatabaseName AS VARCHAR(500)
DECLARE CUR CURSOR
--Choose the DBs on which you would like to run the script
WHERE database_id > 4
--NEXT NEXT RECORD
WHILE @@FETCH_STATUS = 0
DECLARE @DBName AS NVARCHAR(128);
SET @DBName = QUOTENAME(N'' + @DatabaseName + '');
--USE Dynamic SQL To Change DB name and run DDL statement to create Stored Procedure
N'USE ' + @DBName + N'; EXEC('''+@DDL+''');'