How do I determine if a table exists in a SQL Server database?

For normal tables, you can use this query (SQL Server 2000): 
 

IF EXISTS (SELECT 1      FROM INFORMATION_SCHEMA.TABLES      WHERE TABLE_TYPE='BASE TABLE'      AND TABLE_NAME='tablename')          SELECT 'tablename exists.'  ELSE          SELECT 'tablename does not exist.'    -- or    DECLARE @isObject TINYINT, @isTable TINYINT, @objID INT  SET @objID = OBJECT_ID('tablename')  SELECT @isObject = COALESCE(@objID, 0),       @isTable = OBJECTPROPERTY(@objID, 'IsUserTable')  IF @isObject + @isTable = 2      SELECT 'tablename exists.'  ELSE      SELECT 'tablename does not exist.'

 
In SQL Server 7.0, you can query sysobjects: 
 

IF EXISTS (SELECT 1      FROM sysobjects      WHERE xtype='u' AND name='tablename')          SELECT 'tablename exists.'  ELSE          SELECT 'tablename does not exist.'

 
Now you may have been stuck for #temp tables. Many people have tried this: 
 

SELECT * FROM INFORMATION_SCHEMA.TABLES      WHERE TABLE_NAME='#some_temp_name'    -- or    SELECT * FROM sysobjects WHERE name='#tmp'

 
These don't work for two reasons. One is that #temp tables don't live in the current database, but rather in TempDB. So, add the TempDB prefix, or USE TEMPDB statement, right? No, there is still another reason this won't work. Much like session variables in ASP, TempDB needs some way to track #temp tables in the event that several people create #some_temp_name at the same time. So, an identifier is appended to the table name, something like: 
 

#some_temp_name__...many_underscores_removed...__000000000016

 
You can see this name simply by running the following: 
 

USE PUBS  GO    CREATE TABLE #some_temp_name(id INT)  GO    USE TEMPDB  GO    SELECT OBJECT_NAME(OBJECT_ID('#some_temp_name'))    USE PUBS  GO

 
Okay, with all that out of the way, here is how you can use the OBJECT_ID function to determine if a #temp table exists: 
 

IF OBJECT_ID('tempdb..#some_temp_name') IS NOT NULL      PRINT '#some_temp_name exists.'  ELSE      PRINT '#some_temp_name does not exist.'

 
You can use this methodology for normal tables as well, but keep in mind that, just because a name returns a valid OBJECT_ID, doesn't mean that it is a table (unless you have a very adherent development staff, and/or can otherwise guarantee that by naming scheme alone). 
 
Note that you can't use OBJECT_NAME(OBJECT_ID('tempdb..#tempname')) in the context of the current database. The OBJECT_ID will return correctly, since it is handed an object that is clearly in tempdb. However, OBJECT_NAME uses the current context, and is only handed an integer. So you will either end up with NULL, or the name of some object in the current database that corresponds with the id returned from the inner function call. 
 
For information on checking for a table's existence in Microsoft Access, see Article #2350.