For the longest time, I've been using code like this
IF EXISTS (
WHERE object_id = OBJECT_ID(N'[dbo].[FakeProc]')
AND type IN (N'P',N'PC')
DROP PROCEDURE [dbo].[FakeProc]
IF EXISTS (
WHERE [name] = 'trFakeTrigger'
AND type = 'TR'
DROP TRIGGER trFakeTrigger;
to test for existence of a Procedure or Trigger prior to dropping it. The challenge inherent within using the above syntax was remembering the abbreviation used to define the object type.
Later on, I found that I could use
IF OBJECT_ID('FakeProc') IS NOT NULL
DROP PROCEDURE FakeProc
IF OBJECT_ID('dbo.fn_FakeFunction') IS NOT NULL
DROP FUNCTION dbo.fn_FakeFunction
This was certainly more memorable than the earlier version but was still not the best choice. I needed a template that could be easily copied multiple times and modified with minimal issues.
I then discovered the most efficient trick: surround the drop command with a TRY - CATCH block.
BEGIN TRY DROP TRIGGER FakeTrigger END TRY BEGIN CATCH END CATCH;
BEGIN TRY DROP TABLE #Test END TRY BEGIN CATCH END CATCH;
BEGIN TRY ALTER TABLE MyTable DROP CONSTRAINT FakeConstraint END TRY BEGIN CATCH END CATCH;
BEGIN TRY DROP PROCEDURE FakeProc END TRY BEGIN CATCH END CATCH;
The best part is that MSDN already has written the code for a TRY-CATCH snippet. This snippet doesn't come built into SSMS, but the page provides clear and simple instructions for creating and importing the snippet. Give it a try and Happy Coding!