I recently saw the following code:

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;

Published under: SQL Server Tips ·  ·  ·  ·