Scenario: How to drop Stored Procedure from multiple or all Databases in SQL Server
In my last post, I wrote How to create a Stored Procedure in all the databases or multiple databases in SQL Server. You can Click here to see that post.
In this post 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.
In my last post, I wrote How to create a Stored Procedure in all the databases or multiple databases in SQL Server. You can Click here to see that post.
In this post 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
No comments:
Post a Comment