Using the IF EXISTS test when creating a new object in SQL Server

I have been looking up the IF...EXISTS rules every time I use them for so long I am finally putting this into a post so maybe it will solidify in my memory. In essence, IF...EXISTS is a test you should use when creating a new object to ensure you don't get an error because the object already exists when you try to create it. Seems basic but it gets forgotten a lot and is really useful even in a script you;re not compiling to your server. The basic syntax is as follows:

IF EXISTS(SELECT Object_id('Employees')) DROP TABLE dbo.employees 

CREATE TABLE [dbo].[employees] 
  ( 
     [name]      [VARCHAR](50) NOT NULL, 
     [dob]       [SMALLDATETIME] NULL, 
     [pay_rate]  [MONEY] NULL, 
     [hire_date] [SMALLDATETIME] NULL, 
     [ssn]       [INT] NOT NULL 
  ) 
ON [PRIMARY] 

If you are using a temp table, the Object_ID function will not work unless you specify the temp_db database as in this example. OBJECT_ID only returns the ID for schema scoped objects, so for temp tables you must specify the correct catalog (tempdb.sys.all_objects)

IF EXISTS (SELECT object_id 
           FROM   tempdb.sys.all_objects 
           WHERE  name LIKE '#search_results%') 
  DROP TABLE #search_results 

So by having these tests in place, you can re-run your script or procedure as often as you like without worrying about those annoying 'there is already an object named blah blah in the database..." errors. And since you can put whatever action you like after the IF EXISTS test, you can TRUNCATE the table instead of dropping it, you can update it, etc etc.

I used the Instant SQL Formatter to create the code snippets in this post, works great for blogs.