Drop all proc from same database at one time

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Drop all proc from same database at one time

Author

Topic

vedjha
Posting Yak Master

228 Posts

Posted - 2010-08-10 : 01:25:16

Hello sir,

Drop all procedure from same database at one time.
plz help...

http://kiransoftech.com

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2010-08-10 : 01:54:35

try this:


USE YourDataBase;
GO
select 
'drop procedure ' + name 
from sys.objects where type_desc = 'SQL_STORED_PROCEDURE'

Copy/Paste the output and run it.

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2010-08-10 : 01:56:27

Execute the query and copy the result set and execute it again.

Select 'drop procedure '+name from sys.objects where type ='p'

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

Kristen
Test

22859 Posts

Posted - 2010-08-10 : 02:27:19

Or Script the database, using SSMS, and just use the DROP PROCEDURE section of the script.

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-08-10 : 02:30:36

Just Execute the below Script -


DECLARE @SQL AS VARCHAR(MAX)
SET @SQL = ''
select @SQL = @SQL + ' DROP PROCEDURE '+ Name FROM sys.procedures
EXEC ( @SQL )

Vaibhav T

To walk FAST walk ALONE
To walk FAR walk TOGETHER

vedjha
Posting Yak Master

228 Posts

Posted - 2010-08-10 : 02:39:51

I have approax 100 proc and drop them at a time.
Here every solution having 'Name' then it mean i will have to drop proc one by one.

http://kiransoftech.com

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-08-10 : 02:44:14

quote:
Originally posted by vedjha

I have approax 100 proc and drop them at a time.
Here every solution having 'Name' then it mean i will have to drop proc one by one.

http://kiransoftech.com

I am surprised with the red part.
If a single query is removing all the procedure
then what does that mean ?

Vaibhav T

To walk FAST walk ALONE
To walk FAR walk TOGETHER

Kristen
Test

22859 Posts

Posted - 2010-08-10 : 03:39:25

"then it mean i will have to drop proc one by one"

Explain why you have a problem with that please?

If you have a script

DROP PROCEDURE MyProc1
DROP PROCEDURE MyProc2
...

you can just run that (as a single statement if you like).

If you want you could make a Stored Procedure that dynamically executed the code, and thus did the complete job (including deleting itself ...)

But I can't see that there would be demand for an inbuilt command to "DROP PROCEDURE *" ...

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-08-10 : 05:02:30

Have you tried vaibhavktiwari83's code?

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources