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

Scenario:How to Create Same Stored Procedure on All Databases or multiple 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 Statment that you would like to run for Create Stored Procedure in Each Databsae 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