SQL Server: Tips and Tricks - 2 (2017)

How to drop Stored Procedure from All or Multiple databases in SQL Server

Scenario:

You are working as SQL Server DBA or SQL Server developer, you need to come up with scripts to drop the procedure from many databases in one of your SQL Server Instance.

Solution:

We are going to write the script how to drop the Stored Procedure in multiple or all SQL Server databases. You can filter the list of Databases on which you like to run the script by changing the where clause "Select name from sys.databases where *********"

I have cursor to loop through the databases. You need to provide the Schema Name and Stored Procedure that you would like to drop from multiple or all the databases in SQL Server.

--Provide the Stored Procedure Name and Schema Name that you would like to drop from multiple or all databases

Declare @SP_Schema VARCHAR(128)

DECLARE @SP_Name VARCHAR(128)

--Provide Schema Name and Stored Procedure you would like to drop

SET @SP_Schema='dbo'

SET @SP_Name='SP_Test'

DECLARE @DDL VARCHAR(MAX)

SET @DDL='IF (EXISTS (Select * from sys.objects

where name='''''+@SP_Name+'''''

and schema_id=schema_id('''''+@SP_Schema+''''')

and type=''''P''''

and type_desc=''''SQL_STORED_PROCEDURE''''))

BEGIN

Drop Procedure '+@SP_Schema+'.'+@SP_Name+'

END'

Print @DDL

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(500);

SET @DBName = QUOTENAME(N'' + @DatabaseName + '');

--USE Dynamic SQL To Change DB name and run DDL statement to drop Stored Procedure

EXEC (

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

);

FETCH NEXT

FROM CUR

INTO @DatabaseName

END

CLOSE CUR

DEALLOCATE CUR