Database Discussion - Delete Stored Procedure via SQL

Print Page | Close Window

Delete Stored Procedure via SQL

Printed From: Web Wiz Forums
Category: General Discussion
Forum Name: Database Discussion
Forum Description: Discussion and chat on database related topics.
URL: https://forums.webwiz.net/forum_posts.asp?TID=13206
Printed Date: 29 March 2026 at 9:20am
Software Version: Web Wiz Forums 12.08 - https://www.webwizforums.com

Topic: Delete Stored Procedure via SQL

Posted By: dj air
Subject: Delete Stored Procedure via SQL
Date Posted: 04 January 2005 at 10:17am

hi guys,.

does anyone know if you can delete a Stored Procedure using a query. like alter, add a stored procedure. i can't find it anywhere. devguru, didn't help weirdly. or i can't see it. LOL

im making an update script and want to delete all existing SP's then re add the new ones.

Replies:

Posted By: WebWiz-Bruce
Date Posted: 04 January 2005 at 11:27am

Try 'Drop Procedure name'

Replace name with the stored procedures name.

-------------
https://www.webwiz.net/web-wiz-forums/forum-hosting.htm" rel="nofollow - Web Wiz Forums Hosting
https://www.webwiz.net/web-hosting/windows-web-hosting.htm" rel="nofollow - ASP.NET Web Hosting

Posted By: Mart
Date Posted: 04 January 2005 at 11:35am

If you are using SQL Server (it sounds like you are) you can let enterprise manager do an upgrade script for you and write all of that repetitive code

Posted By: dj air
Date Posted: 04 January 2005 at 12:34pm

yea is SQL server.

i want a asp page like MS_Server_setup.aspwhich will upgrade my V2 application to V3. add a few fields etc that ok.

i want ed to delete all existing SP's and add the new ones, thanx boRg ill try that.

Posted By: Mart
Date Posted: 04 January 2005 at 12:52pm

One way to do it is to open up a V2 database in enterprise manager and create a new database diagram.... then using the diagram add all the columns and alter everything that changed between the V2 database and V3... then you just click Generate Update Script on the toolbar or something like that and it will script all the changes for you. At least thats how http://linkmanager.devjunkies.com - http://linkmanager.devjunkies.com upgrades work.

Posted By: michael
Date Posted: 04 January 2005 at 10:43pm

If your sp's have a certain prefix and you want to make sure all are gone before you re-create them you can also run some query like

Declare @prc varchar(150)
While (Select Count(*) from sysobjects where name Like 'djair_%' and xtype = 'P') > 0
Begin
Set @prc = (Select 1 [name] from sysobjects where name LIKE 'djair_%' and xtype='P')
Set @prc = 'Drop Procedure ' + @prc
Exec(@prc)
END

-------------
http://baumannphoto.com" rel="nofollow - Blog | http://mpgtracker.com" rel="nofollow - MPG Tracker

Posted By: dj air
Date Posted: 05 January 2005 at 6:29am

cheers michael, thats what i want, Wink

they have a prefix. so that will help.

ill have ago with that. cheers again.

Posted By: dj air
Date Posted: 05 January 2005 at 4:52pm

hi michael, not sure whats going on here, might be my mistake

do i need to create a stored procedure to delete the others or would something like this work.

        'delete all stored procedures
        strSQL = "Declare @prc varchar(150) "
        strSQL = strSQL & "While (Select Count(*) from sysobjects where name Like '" & strSPPrefix & "%' and xtype = 'P') > 0 "
        strSQL = strSQL & "Begin "
        strSQL = strSQL & "Set @prc = (Select 1 [name] from sysobjects where name LIKE '" & strSPPrefix & "%' and xtype='P') "
        strSQL = strSQL & "Set @prc = 'Drop Procedure ' + @prc "
        strSQL = strSQL & " Exec(@prc) "
        strSQL = strSQL & "END "

        'Write to the database
        adoCon.Execute(strSQL)

because i have tried the above and when it goes to do it, it just hangs, there are a few Stored procedures but not as many as trhe forum.

thanx in advanced,

its hanging now, been a couple minutes.

Posted By: Mart
Date Posted: 05 January 2005 at 4:56pm

Thats how you should run it, not as a procedure... let me try it out on my local server in query analyser debug mode

Posted By: Mart
Date Posted: 05 January 2005 at 5:02pm

Looks good for me, try doing a Response.WRite(strSQL) to check you copied it correctly

Posted By: dj air
Date Posted: 05 January 2005 at 5:12pm

weird, the page wont load now,
just hangs al to gether, but a friend said it works on hers.. weird

the whole domain is hanging, even when not in the folder

http://support.emagine-hosts.com/tv/msSQL_server_setup.asp

Posted By: dj air
Date Posted: 05 January 2005 at 5:20pm

managed to get an out put,.



Declare
@prc varchar(150) While (Select Count(*) from sysobjects where name
Like 'BTSPTemp%' and xtype = 'P') > 0 Begin Set @prc = (Select 1
[name] from sysobjects where name LIKE 'BTSPTemp%' and xtype='P') Set
@prc = 'Drop Procedure ' + @prc Exec(@prc) END

BTSPTemp is the prefix of the SP's still just hangs

Posted By: Mart
Date Posted: 05 January 2005 at 5:32pm

you kept the % in, right?

Posted By: Mart
Date Posted: 05 January 2005 at 5:33pm

If you put the prefix name in incorrectly (i.e. no procedures begin with it in the current database) it will effectively be an eternal loop

Posted By: dj air
Date Posted: 05 January 2005 at 6:11pm

cheers,

ill check to make sure the procedures are existing.

also regarding " you kept the % in, right?"

the above statement is what i have as the query

Posted By: dj air
Date Posted: 05 January 2005 at 7:12pm

the procedures are on the server, and with the same prefix as above.

Posted By: dj air
Date Posted: 06 January 2005 at 1:30pm

ive fixed it. the code was written wrong, made a concinuous loop it was ment to be.




          'delete all stored procedures


          strSQL = "Declare @prc varchar(150) "


strSQL = strSQL & "While (Select Count(*) from sysobjects where
name Like '" & strSPPrefix & "%' and xtype = 'P') > 0 "


          strSQL = strSQL & "Begin "


strSQL = strSQL & "Set @prc = (Select MIN([name]) from sysobjects
where name LIKE '" & strSPPrefix & "%' and xtype='P') "


          strSQL = strSQL & "Set @prc = 'Drop Procedure ' + @prc "


          strSQL = strSQL & " Exec(@prc) "


          strSQL = strSQL & "END "

or set the bold to TOP 1,

before it was making a field called name with the value 1, in a loop.

thanx anyway, also let you know. Wink