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”.