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.

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.