Drop stored procedures by...

Hi,
How do I drop all stored procedures with the prefix of "www_".
Basically, I need to add all new dev DB sprocs to our prod DB, but on the
prod DB there are a whole bunch of old sprocs that needs to be dropped.
Thanks in advance
ChristianFrom Vyas:
On SQL Server 2000 the following will give you a list of all stored
procedure names in the database:
SELECT ROUTINE_SCHEMA AS Owner,
ROUTINE_NAME AS ProcedureName
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_TYPE = 'PROCEDURE'
The following will return the DROP PROC commands that you need to run:
SELECT 'DROP PROC ' + QUOTENAME(ROUTINE_SCHEMA) + '.' +
QUOTENAME(ROUTINE_NAME) AS Command
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_TYPE = 'PROCEDURE'
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Christian Perthen" <abracadabara@.dontreplytothidress.com> schrieb im
Newsbeitrag news:e2VlEOESFHA.248@.TK2MSFTNGP15.phx.gbl...
> Hi,
> How do I drop all stored procedures with the prefix of "www_".
> Basically, I need to add all new dev DB sprocs to our prod DB, but on the
> prod DB there are a whole bunch of old sprocs that needs to be dropped.
> Thanks in advance
> Christian
>|||Must be sure changed in some way, sorry i forgot:

> SELECT ROUTINE_SCHEMA AS Owner,
> ROUTINE_NAME AS ProcedureName
> FROM INFORMATION_SCHEMA.ROUTINES
> WHERE ROUTINE_TYPE = 'PROCEDURE' AND ROUTINE_NAME LIKE 'www_%'
> The following will return the DROP PROC commands that you need to run:
> SELECT 'DROP PROC ' + QUOTENAME(ROUTINE_SCHEMA) + '.' +
> QUOTENAME(ROUTINE_NAME) AS Command
> FROM INFORMATION_SCHEMA.ROUTINES
> WHERE ROUTINE_TYPE = 'PROCEDURE' AND ROUTINE_NAME LIKE 'www_%'
>
Jens Suessmeyer.
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> schrieb
im Newsbeitrag news:uLPtCWESFHA.1996@.TK2MSFTNGP10.phx.gbl...
> From Vyas:
> On SQL Server 2000 the following will give you a list of all stored
> procedure names in the database:
> SELECT ROUTINE_SCHEMA AS Owner,
> ROUTINE_NAME AS ProcedureName
> FROM INFORMATION_SCHEMA.ROUTINES
> WHERE ROUTINE_TYPE = 'PROCEDURE' AND ROUTINE_NAME LIKE 'www_%'
> The following will return the DROP PROC commands that you need to run:
> SELECT 'DROP PROC ' + QUOTENAME(ROUTINE_SCHEMA) + '.' +
> QUOTENAME(ROUTINE_NAME) AS Command
> FROM INFORMATION_SCHEMA.ROUTINES
> WHERE ROUTINE_TYPE = 'PROCEDURE' AND ROUTINE_NAME LIKE 'www_%'
>
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
> "Christian Perthen" <abracadabara@.dontreplytothidress.com> schrieb im
> Newsbeitrag news:e2VlEOESFHA.248@.TK2MSFTNGP15.phx.gbl...
>

Drop a Temp Table

Using SQL Server 2005. I have a stored prod that uses a temp table. I need to test at the start of the prod to see if the temp table is there. Using the following code at the start of my prod, but does not run.

IF exists(select * from ##TO_STATUS_TBL)
DROP TABLE ##TO_STATUS_TBL

What is the best way to check and/or drop the temp table.
Thank you, DavidYou do not need to test if the temporary table is there. Temporary tables exist only within the scope of the procedure, and are automatically dropped when it completes. (OK, the exception would be global temporary tables, but you probably shouldn't be using those anyway...).|||Looks like a global temporary table. I am not sure if it is strictly required, but if it is, how do you determine if other processes still need it?

Anyway. This code should drop it every time

if exists (select * from tempdb..sysobjects where name like '##TO_STATUS_TBL%')
begin
drop table ##TO_STATUS_TBL
end

If I were you, I would look into whether the global part of the temp table is really needed. And if not, make this a local temp table.

EDIT: Fixed typo in code.|||Thank you all for your help. Did not see that I was using a globe temp table. Changed code so that I am not using a globe table. Code runs fine. David|||I usually do the following:

if object_id( '#table' ) is not null
begin
drop table #table
end|||You do not need to test if the temporary table is there. Temporary tables exist only within the scope of the procedure, and are automatically dropped when it completes. (OK, the exception would be global temporary tables, but you probably shouldn't be using those anyway...).

As far my knowledge says, that the scope of the #TempTable is for the Connection....... Not restricted to the SP only. Hence even if the SP is executed, #TempTable will remain there in the temp DB until and unless the DB connection is lost. With the same DB connection, I can run many SPs one after another, and the #TempTable will remain as it is till either will explicitly drop it or we loose the DB connection.

Thanks,
Rahul Jha|||No Guys, I was wrong....... You were right BlindMan

Thanks,
Rahul Jha|||I usually do the following:

if object_id( '#table' ) is not null
begin
drop table #table
end
Shouln't this be:
if object_id( 'tempdb..#table' ) is not null
...|||Shouln't this be:
if object_id( 'tempdb..#table' ) is not null
...

I believe a table preceded by the '#' will always be assumed to be in tempdb, as user sprocs with 'sp_' are always checked to be in master.|||As far my knowledge says, that the scope of the #TempTable is for the Connection....... Not restricted to the SP only. Hence even if the SP is executed, #TempTable will remain there in the temp DB until and unless the DB connection is lost. With the same DB connection, I can run many SPs one after another, and the #TempTable will remain as it is till either will explicitly drop it or we loose the DB connection.

Thanks,
Rahul Jha

Temp table #TempTable will have a name like #TempTable______2344237748 in tempdb..sysobjects.|||I believe a table preceded by the '#' will always be assumed to be in tempdb, as user sprocs with 'sp_' are always checked to be in master.
True, but unfortunately not for the function OBJECT_ID. If you run the test below you will see that the first statement returns NULL and the second the object id.

CREATE TABLE #tst (c INT)

SELECT OBJECT_ID('#tst')
UNION
SELECT OBJECT_ID('tempdb..#tst')

DROP TABLE #tst