MSSQL - Delete ALL Stored Procedures?

General Web Coding Microsoft Development
1

hi guys,
im having a bit of trouble.

i have this script from a friend

      '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 "

its ment to delete all the stored Procedures with a set begining.

but it hangs all the time, i think its going into a continuos loop. the prefix im checking for exists. can anyone sheed any light on this?

2

[QUOTE=AaronHoc]

* strSQL = "Declare @prc varchar(150) "

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

* strSQL = strSQL & “Set @prc = (Select 1 [name] from sysobjects where name LIKE '” & strSPPrefix & "%’ and xtype=‘P’)

but it hangs all the time, i think its going into a continuos loop. the prefix im checking for exists. can anyone sheed any light on this?
[/QUOTE]

I tagged three things that you might want to look into. The first thing that I would do is run these queries in an external analyzer or dump the contents of the recordset.

Also, make sure that you have the correct permissions to drop the procedures. If your script is picking up system stored procedures then it’s going to fail.

My question is why would you want to do something like this?

3

thanx,

ill check the permssions, ive checked it in a query analizer and it seems ok.

regards to Why.

i make asp applications. one of which is a tv guide for websites to use on their websites.

im making a V3 of the application, and to make it easier for the application users, im making an update script.

this script adds the extra fields required (that works)

then delete existing stored procedures, and adds the updated versions of them.

if i can’;t get it to work ill have to do each one seperatlly.

4

The error is in this line:

strSQL = strSQL & “Set @prc = (Select 1 [name] from sysobjects where name LIKE '” & strSPPrefix & "%’ and xtype=‘P’) "

The part that reads “Select 1 [name]” should read “Select TOP 1 [name]” or “Select MIN([name])”. The intent is to get one record that matches, but the syntax used actually generates a constant column ‘name’ with a value of 1 for every matching record in the sysobjects table. I.e., the result set being returned by the code above is:

name

1
1
1
1
1
1
…

Instead of:

name

myStoredProcName

5

thanx, ill try that.

cheers for the help

6

thanx works like a charm

7

USE ThisOneDB /*** Change to your Database name ***/
GO
SELECT ‘Drop Procedure ’ + SCHEMA_NAME(schema_id)+’.'+name AS Drop_procedure_SQL
FROM sys.procedures

/*** Run the results on your Database /
/(Example of the result)***/
Drop Procedure dbo.IBS
Drop Procedure dbo.Insert_IBS
Drop Procedure dbo.Delete_IBS