Recently I was trying to remember the code to check and see if a procedures exists before I do something with it and I did a search on the internet to see what came up. The most common response that I saw was:

view plain print about
1if exists (
2 select * from dbo.sysobjects
3 where id = object_id(N'[dbo].[usp_my_dummy_proc]')
4 and OBJECTPROPERTY(id, N'IsProcedure') = 1
5 )
6    select 'IT EXISTS!';
Looking at this code, a couple of things are happening, first the object id is collected and then used to find the object in sysobjects. Next it is validating that it is a procedure.

For my purposes though, this was overkill. I was already creating a procedure using a template and one of the last steps I wanted to do was change the permissions. However if there is an error creating the procedure I didn't want the permission change to generate another error. In this case, I didn't need to check if it was a procedure because I was creating it right then. As I looked at the code, I also noticed something else, there are two hits being made just to check for existence when all that is needed is one. I say there are two because object_id(N'object_name') will return an id if something exists and a null if it doesn't.

Therefore, you can save on excess processing if you just want to know if something is there by doing the following:

view plain print about
1IF (OBJECT_ID(N'[dbo].[usp_my_dummy_proc]') IS NOT NULL)

Just another little code snippet to help when you need to just know if it exists.