Generating the Code to Remove All Procedures in SQL Server

Hello everyone,

In this article, I will talk about how to dynamically generate code for removing all procedures in SQL Server.

In SQL Server, in some cases, instead of removing the procedures by manually writing the code, we can remove the relevant procedures by dynamically generating code.

You can see the relevant code below.

SELECT 'DROP PROC ' + '[' + ROUTINE_SCHEMA + ']' + '.' + '[' + ROUTINE_NAME + ']'
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_TYPE = 'PROCEDURE';

When we run the relevant code on the Northwind database, you will see the following result.

Generating the Code to Remove All Procedures in SQL Server

As you can see, the following codes were generated.

DROP PROC [dbo].[Ten Most Expensive Products];
DROP PROC [dbo].[Employee Sales by Country];
DROP PROC [dbo].[Sales by Year];
DROP PROC [dbo].[CustOrdersDetail];
DROP PROC [dbo].[CustOrdersOrders];
DROP PROC [dbo].[CustOrderHist];
DROP PROC [dbo].[SalesByCategory];
DROP PROC [dbo].[sp_upgraddiagrams];
DROP PROC [dbo].[sp_helpdiagrams];
DROP PROC [dbo].[sp_helpdiagramdefinition];
DROP PROC [dbo].[sp_creatediagram];
DROP PROC [dbo].[sp_renamediagram];
DROP PROC [dbo].[sp_alterdiagram];
DROP PROC [dbo].[sp_dropdiagram];
DROP PROC [dbo].[ScriptCreateTableKeys];
DROP PROC [dbo].[ScriptCreateTableKeys2];

You can run whatever you want according to your needs, or you can run them all together and remove all procedures.

Good luck to everyone in business and life.

132 Views

Yavuz Selim Kart

I try to explain what I know in software and database. I am still improving myself by doing research on many programming languages. Apart from these, I am also interested in Graphic Design and Wordpress. I also have knowledge about SEO and Social media management. In short, I am a determined person who likes to work hard.

You may also like...

Don`t copy text!