SQL query to get the list of user defined stored procedures in SQL Server

To get the list of all Stored procedures written by user for its database operation, following query is useful.

select * from [myDataBaseName].sys.procedures where is_ms_shipped = 0
order by name



If you want to get the all list of stored procedures just use the following query.

SELECT NAME FROM [myDataBaseName].sys.all_objects WHERE type='P'

After getting the required result from database we can make use this result as per our requirement.
If we want to delete/drop all stored procedures we defined previously, just write the cursor to drop the stored procedures from list.

I have written a small cursor to delete user defined stored procedures one-by-one.

--====================================
-- Author : Gajanan
-- Description :
--====================================
create procedure procedure_TODeleteAllSps
as
begin
set nocount on
declare  @ProcName  nvarchar(100)

declare c1 cursor for SELECT name FROM [MydbName].sys.procedures WHERE is_ms_shipped = 0
ORDER BY name
open c1
fetch next from c1 into @ProcName
while @@FETCH_STATUS =0
begin
exec ('drop procedure' + @ProcName)

fetch next from c1 into @ProcName
end
close c1
deallocate c1
end

I hope this post will help you.

Regards,
Gajanan