SQL Server: Easily detect if a table or other object exists

Generally people write as below to check whether table or object exists:


IF  EXISTS (SELECT * FROM sys.objects
WHERE object_id = OBJECT_ID(N'[dbo].[TableName]')
AND type in (N'U'))
  DROP TABLE [dbo].[TableName];

However, this is more complicated than needed. You can just do this:

IF Object_ID('dbo.TableName', 'U') IS NOT NULL DROP TABLE dbo.TableName;

The second parameter of Object_ID accepts any value from the xtype column of the sysobjects table. U is for user table.

To check for temp tables and temp stored procedures, do this:

IF Object_ID('tempdb.dbo.#TableName', 'U') IS NOT NULL DROP TABLE #TableName;