Hi all,

does anyone know of an easy way to drop all the user stored procedures from a sql2k db in one go?

cheers,
kenny

Hi,

I am just rushing home at the end of the day, so let me give you a short explanation on how to go about. Sorry, if I haven't given you the entire explanation on how to do it.

The following query will give you all the Stored Procedures.
You can loop through them using WHILE construct and drop them once by one. Here you go,

select a.name from sysobjects a, sysusers b, master..syslogins c where b.suid=c.suid and a.uid=b.uid and c.loginname="<USER_NAME>" and a.type="P"

<USER_NAME> is what the USER you meant. First run this Query carefully and check if the SPs listed are all the User SPs. Then go on to drop them.

Really sorry if I have not given you enough information.

May be some one got a better answer.

Cheers!!!

-Vijay G

Thanks for that, will have a go & see what happens.
Will base it around...

SELECT name FROM sysobjects WHERE type = 'P' And category = 0

...as i want to drop all non-system procs.

Thanks again,
Kenny

ended up with this which seems to work...

/* Drop all non-system stored procs */

declare @name varchar(128)
declare @SQL varchar(254)


SELECT @name = (select top 1 name FROM sysobjects WHERE type = 'P' And category = 0 order by name)
while @name is not null
begin

select @SQL = 'drop procedure [dbo].[' + rtrim(@name) +']'
exec (@SQL)

print 'Dropped :' + @name

SELECT @name = (select top 1 name FROM sysobjects WHERE type = 'P' And category = 0 and name > @name)
end

go

Yes, you are right Kenny. I got it the way you wanted to delete SPs based on USERNAME/LOGINNAME.

Cheers,

-Vijay G

This works a bit better (notice I added an order by name)

/* Drop all non-system stored procs */

declare @name varchar(128)
declare @SQL varchar(254)


SELECT @name = (select top 1 name FROM sysobjects WHERE type = 'P' And category = 0 order by name)
while @name is not null
begin

select @SQL = 'drop procedure [dbo].[' + rtrim(@name) +']'
exec (@SQL)

print 'Dropped :' + @name

SELECT @name = (select top 1 name FROM sysobjects WHERE type = 'P' And category = 0 and name > @name order by name)
end

go

Nice to see someone look into it after 3 and half year ;)

cheers.

_________________________
- Vijay G
Strive for Perfection