Dropping all procedures of a particular user? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

Dropping all procedures of a particular user?

Author

Topic

dmilam
Posting Yak Master

185 Posts

Posted - 2010-04-14 : 12:30:02

Hello,

How to drop all stored procedures created by a certain user, across all databases? I don't see anything on MSDN which is helpful.

In this case, I want to drop all procedures I've created under my own login credentials -- CORP\me.procedure1, CORP\me.procedure2, etc. -- without listing each one out separately like I've started to do below... is it possible?

IF
EXISTS
(
SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[CORP\me].[procedure1]')
AND type in (N'P', N'PC')
)
DROP PROCEDURE [CORP\me].[procedure1]

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2010-04-14 : 12:48:41

check if this works for you: http://www.sqlservercentral.com/scripts/Administration/63631/

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

dmilam
Posting Yak Master

185 Posts

Posted - 2010-04-14 : 12:53:12

Thanks, but I'd rather not register for yet another site. I'll keep digging.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-04-15 : 07:28:56

Run this

select 'select ''drop procedure ''+routine_name from '+name+'.information_schema.routines
where specific_schema=''[CORP\me]''' from master..sysdatabases

Copy the result and run them

If you are able to see all the procedures created by [CORP\me] with drop commands, execute them

Madhivanan

Failing to plan is Planning to fail

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2010-04-15 : 07:59:35

"Thanks, but I'd rather not register for yet another site. I'll keep digging."

It's free. It's a very well run site - you'll find a reasonable overlap in the membership of the top-poster on both sites. And you can unregister as well.

dmilam
Posting Yak Master

185 Posts

Posted - 2010-04-15 : 11:54:16

quote:
Originally posted by madhivanan

Run this

select 'select ''drop procedure ''+routine_name from '+name+'.information_schema.routines
where specific_schema=''[CORP\me]''' from master..sysdatabases

Copy the result and run them

If you are able to see all the procedures created by [CORP\me] with drop commands, execute them

Thanks; running this, I only get "Command(s) completed successfully", rather than seeing all the procedures created by [CORP\me]. I'm not the DBA and am only using SSMS Express, if that matters. Well, the first point matters :-)

Being new to this, I definitely do not want to be deleting any procedures not created by [CORP\me].

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-04-16 : 04:56:57

quote:
Originally posted by dmilam

quote:
Originally posted by madhivanan

Run this

select 'select ''drop procedure ''+routine_name from '+name+'.information_schema.routines
where specific_schema=''[CORP\me]''' from master..sysdatabases

Copy the result and run them

If you are able to see all the procedures created by [CORP\me] with drop commands, execute them

Thanks; running this, I only get "Command(s) completed successfully", rather than seeing all the procedures created by [CORP\me]. I'm not the DBA and am only using SSMS Express, if that matters. Well, the first point matters :-)

Being new to this, I definitely do not want to be deleting any procedures not created by [CORP\me].

It means that you dont have any schema like [CORP\me]

Madhivanan

Failing to plan is Planning to fail

dmilam
Posting Yak Master

185 Posts

Posted - 2010-04-16 : 14:05:05

OK, thanks. I guess I've misunderstood [CORP\me] as applying to all the procedures that I've created.

Running your suggested query works today, for whatever reason, but when I run the results, I get "0 rows affected". For example in the T database, running:

select 'drop procedure '+routine_name from T.information_schema.routines
where specific_schema='[CORP\me]'

But, I can see the stored procedures via Object Explorer in the T database, listed as [CORP\me].procedure1, etc.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-04-19 : 05:41:22

quote:
Originally posted by dmilam

OK, thanks. I guess I've misunderstood [CORP\me] as applying to all the procedures that I've created.

Running your suggested query works today, for whatever reason, but when I run the results, I get "0 rows affected". For example in the T database, running:

select 'drop procedure '+routine_name from T.information_schema.routines
where specific_schema='[CORP\me]'

But, I can see the stored procedures via Object Explorer in the T database, listed as [CORP\me].procedure1, etc.

See what this returns

select distinct specific_schema from information_schema.routines

Madhivanan

Failing to plan is Planning to fail

dmilam
Posting Yak Master

185 Posts

Posted - 2010-04-19 : 12:38:30

This returns:

specific_schema
------------------------------------------------------------
dbo

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-04-19 : 13:50:08

Are you sure you are running it in the correct database? Put a "USE dbName GO" command at the top.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

dmilam
Posting Yak Master

185 Posts

Posted - 2010-04-19 : 14:12:51

Thanks. Using

USE T
GO

yields

specific_schema
-----------------------------------------
dbo
CORP\me

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-04-19 : 14:17:18

Cool, you're in the correct database now.

select 'DROP PROC ' + specific_schema + '.' + routine_name
from information_schema.routines
where specific_schema = '[CORP\me]'

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

dmilam
Posting Yak Master

185 Posts

Posted - 2010-04-19 : 16:30:54

quote:
Originally posted by tkizer

Cool, you're in the correct database now.

select 'DROP PROC ' + specific_schema + '.' + routine_name
from information_schema.routines
where specific_schema = '[CORP\me]'

Strange, I get 0 results. However, if I do

SELECT 'DROP PROC ' + '[CORP\me]' + '.' + routine_name
from information_schema.routines

Then I can see the results, such as

DROP PROC [CORP\me].procedure1
DROP PROC [CORP\me].procedure2
etc.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-04-19 : 16:40:13

Remove the square brackets from the WHERE clause.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-04-19 : 16:41:55

select 'DROP PROC ' + specific_schema + '.' + routine_name
from information_schema.routines
where specific_schema = 'CORP\me'

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

dmilam
Posting Yak Master

185 Posts

Posted - 2010-04-19 : 17:28:15

quote:
Originally posted by tkizer

select 'DROP PROC ' + specific_schema + '.' + routine_name
from information_schema.routines
where specific_schema = 'CORP\me'

Thanks; that works. To drop them, however, I have to put the brackets back in, so that CORP\me becomes [CORP\me] again.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-04-19 : 17:29:21

select 'DROP PROC [' + specific_schema + '].' + routine_name
from information_schema.routines
where specific_schema = 'CORP\me'

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

dmilam
Posting Yak Master

185 Posts

Posted - 2010-04-19 : 18:45:36

quote:
Originally posted by tkizer

select 'DROP PROC [' + specific_schema + '].' + routine_name
from information_schema.routines
where specific_schema = 'CORP\me'

Perfect; thanks Tara!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-04-19 : 18:49:11

You're welcome, glad to help.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources