Drop multple procedures (SQL2005)

Question

Hi, I am curious whether I can drop multiple procedures by simple using "%"? Like:

DROP constantName%

When I use DROP in the Management studio, it looks like that:

/****** Object:  StoredProcedure [dbo].[x]    Script Date: 02/02/2010 09:36:25 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[x]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[x]

Why it is checking the object ID when I am dropping this particular object? Thanks

Answer 1

The OBJECT_ID returns the database object identification number of a schema-scoped object. In your code it is checking it inside an IF EXISTS, so that it will only DROP the stored proc if is present in the database.

You could just have DROP PROCEDURE proc_name, but you could end up getting an error if the procedure does not exist. Its just good practise to check before you remove.

Tables, View, Stored Procs etc all have an OBJECT_ID as a key identifier.

I do not believe you can drop mutiple stored procedures using a LIKE. (Though i;m not 100% certain on that)

Answer 2

I am afraid you cant use the like syntax in DROP, below the simple one liner to drop multiple procedures.

   DROP PROCEDURE testest,testest1 

And for your 2nd question. There are scenarious we can create Storedprocedure that can be accessible only to the particular role.

create proc dbo.testest
as
begin
    select 1
end

query sys.objects with the role value

select * from sys.objects where name ='dbo.testest'

it returns null

   select * from sys.objects where name ='testest'

now it works

sys.objects catalog view stores the information without the role names (only just names). But using OBJECT_ID, we can retrieve the role specific info

   select OBJECT_ID('dbo.testest') //works
   select OBJECT_ID('testest') //works

hope you can understand now.

Cheers

RameshVel

Answer 3

I think SQL was designed with this limitation, the drop procedure command needs a string constant. You can't pass a variable as an argument either, it just gives a 'incorrect syntax' error when you do.

Also the OBJECT_ID function only returns a valid id of objects the user owns, or has permission to. So OBJECT_ID is used because it performs the security validation.

Deleting with just "WHERE name = 'object_name'" could also work, but only if the user has permission for that object.