Deleting stored procedures - SQL Server Forums

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

SQL Server 2005 Forums

Transact-SQL (2005)

Deleting stored procedures

Author

Topic

danasegarane76
Posting Yak Master

242 Posts

Posted - 2009-02-11 : 01:48:59

Hi all,
I have nearly 20 stored procedures to delete from My Database. How can I do that using a batch like command ?

Thanks in advance

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2009-02-11 : 02:09:35

You could write a query like:

   select 'drop procedure ' + name from sys.procedures where type = 'P'

and then, copy the output of the query and execute it.

Even though this may seem "quick and dirty", it has the advantage that you can see exactly what you are dropping before you actually do it.

danasegarane76
Posting Yak Master

242 Posts

Posted - 2009-02-11 : 02:19:32

Thanks for the reply Sunitha

But i got the error as

Msg 208, Level 16, State 1, Line 1
Invalid object name 'sys.procedures'.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2009-02-11 : 02:25:16

You could try one of these:


select 'drop procedure ' + name from sysobjects where xtype = 'P'

select 'drop procedure ' + routine_name from INFORMATION_SCHEMA.ROUTINES where routine_type = 'PROCEDURE'

danasegarane76
Posting Yak Master

242 Posts

Posted - 2009-02-11 : 02:34:10

Looks good. I will try and come back :)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-11 : 03:27:05

quote:
Originally posted by danasegarane76

Thanks for the reply Sunitha

But i got the error as

Msg 208, Level 16, State 1, Line 1
Invalid object name 'sys.procedures'.

are you using sql 2005?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources