It is always a good idea to delete a table, especially for temporary tables, before you start creating one. The question is how do you determine if a table exists?
Two methods will be examined, INFORMATION_SCHEMA and OBJECT_ID.
INFORMATION_SCHEMA
This is the easiest and most forward compatible way to get what you want. It is actually a view based on existing stored procedures/functions.The schema describes the structure of the database, including tables, view, etc. It will tell you for example, a list of tables, a list of the parameters of a particular stored procedure, a list of all user-defined functions, and so on.
IF (EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE SCHEMA_NAME = 'MySchema' AND TABLE_NAME = 'MyTable'))
BEGIN
--Do Stuff
END
To get a list of stored procedures/functions.
SELECT ROUTINE_NAME, ROUTINE_DEFINITION FROM INFORMATION_SCHEMA.ROUTINES -- 'FUNCTIONS'
For curious mind wants to know how INFORMATION_SCHEMA really works, here is the way to see how Microsoft implemented it
execute sp_helptext "information_schema.tables"
-- Identifies tables accessible to the current user
CREATE VIEW INFORMATION_SCHEMA.TABLES
AS
SELECT
db_name() AS TABLE_CATALOG,
s.name AS TABLE_SCHEMA,
o.name AS TABLE_NAME,
CASE o.type
WHEN 'U' THEN 'BASE TABLE'
WHEN 'V' THEN 'VIEW'
END AS TABLE_TYPE
FROM
sys.objects o LEFT JOIN sys.schemas s
ON s.schema_id = o.schema_id
WHERE
o.type IN ('U', 'V')
OBJECT_ID
It actually is a system stored procedure. An issue of being a sys sproc is that it tend to change version by version, in terms of input parameters and return value(s). It will tell you if the object exists but not guarantee it is a table type. there is the syntax.
IF OBJECT_ID('*objectName*') IS NOT NULL
When a temporary table name is specified, the database name must come before the temporary table name, unless the current database is tempdb. For example: SELECT OBJECT_ID('tempdb..#mytemptable').
Conclusion:
Use INFORMATION_SCHEMA whenever possible and only use systems stored procedure like OBJECT_ID, when the info you want is not in the INFORMATION_SCHEMA.