This is just an FYI, I’m sure most people know about this but I thought I’d blog about it because I’ve noticed a lot of stored procedures in the database at work that don’t take the schema into account when they drop the stored procedure.

When dropping some object from the database, e.g. a table or stored procedure, the following statement is often used:

IF EXISTS (
        SELECT *
        FROM sysobjects
        WHERE type = 'P' AND name = 'GetAlertGeographies' )
BEGIN
    DROP PROCEDURE [Members].[GetAlertGeographies]
END
GO

Now this might appear ok but it is not; the select statement that is checking the sysobjects table is not taking the schema into account. So this statement could attempt to drop the stored procedure even though it doesn’t exist because a stored procedure with the same name exists in a different schema.

This is the correct statement to use to take schemas into account:

IF EXISTS (
        SELECT TOP 1 NULL
        FROM information_schema.routines
        WHERE specific_name = 'GetAlertGeographies' AND specific_schema = 'Members' )
BEGIN
    DROP PROCEDURE [Members].[GetAlertGeographies]
END
GO

If you want to check for a table just change “routines” to “tables”.