delete all procedures from my database

[Zeyad Jalil]

hi all i want sql statement to delete and drop all my procedures in a data base in sql server 2005 how i can do that? thanks for all

[R Giskard Reventlov]

Does that include the stored proc you want that deletes all of the stored procs? You need to do this drop procedure [proc_name] to drop a stored proc. Create one line for each procedure unless you want to get all of the stored procs without knowing in advance what they are called. I'll leave you to work that one out. Hint: sysobjects

"If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." Red Adair. nils illegitimus carborundum me, me, me

[dravidaveera]

to delete all the procedure in your specified database, just execute the following line. 1. select 'drop proc '+name from sys.objects where type='p' you will get list of procedure like drop table proc1 drop table proc2 select all and execute thats all

RAVIKUMAR S BANGALORE

[Mycroft Holmes]

Create a cursor that loops through the system view INFORMATION_SCHEMA.ROUTINES or sysobjects Get the name of the proc in the cursor construct a drop procedure string DROP PROCEDURE [ProcName] execute the string

Never underestimate the power of human stupidity RAH

[Corporal Agarn]

I would suggest backing up the database before you start. In object explorer go to the database go to the Programmability then Stored Procedures. Open the Object Explorer Details. You can now script all the stored procedures so that when you delete them you have a copy. Highlight the stored procedures to be deleted Right click Select Script Stored Procedure As/Create To/File Give file name. Now you can delete the files that are highlighted by right clicking and selecting delete.

[S Douglas]

DECLARE TblCursor CURSOR FOR

SELECT 
	\[name\] 
FROM 
	sys.objects
WHERE 
	\[name\] like '%' + @fnd + '%'
	and is\_ms\_shipped = 0
	and \[type\] = 'P'

OPEN TblCursor
FETCH next FROM TblCursor INTO @proc
WHILE @@fetch\_status = 0
BEGIN
	
	SET @sql = 'DROP PROCEDURE ' + @proc 

	EXEC @sql

	FETCH next FROM TblCursor INTO @proc
END
CLOSE TblCursor
DEALLOCATE TblCursor

---

Common sense is admitting there is cause and effect and that you can exert some control over what you understand.

[tarun_j200]

DECLARE @sProcName SYSNAME DECLARE @iRowCnt INT, @i INT = 1, @sSQL VARCHAR(255) DECLARE @tblProc TABLE (Id INT IDENTITY(1,1), Name SYSNAME) INSERT INTO @tblProc (Name) SELECT name FROM sys.procedures SET @iRowCnt = @@ROWCOUNT WHILE @i <= @iRowCnt BEGIN SET @sProcName = (SELECT Name FROM @tblProc WHERE Id = @i) SET @sSQL = 'DROP PROC '+@sProcName PRINT 'Procedure '+@sProcName+' deleted.' EXEC (@sSQL) SET @i = @i + 1 END