SQL Server: Tips and Tricks - 2 (2017)

How to Create Same Stored Procedure on All Databases in SQL Server

Scenario:

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.

Solution:

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)

SET @DDL=

'Create Procedure dbo.SP_Test

AS

BEGIN

--My all statements in SP

Select 1

Print ''''Test''''

END'

DECLARE @DatabaseName AS VARCHAR(500)

--DECLARE CURSOR

DECLARE CUR CURSOR

FOR

--Choose the DBs on which you would like to run the script

SELECT NAME

FROM sys.databases

WHERE database_id > 4

--OPEN CURSOR

OPEN CUR

--NEXT NEXT RECORD

FETCH NEXT

FROM CUR

INTO @DatabaseName

WHILE @@FETCH_STATUS = 0

BEGIN

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

EXEC (

N'USE ' + @DBName + N'; EXEC('''+@DDL+''');'

);

FETCH NEXT

FROM CUR

INTO @DatabaseName

END

CLOSE CUR

DEALLOCATE CUR