Thursday, July 31, 2008

TSQL Drop Table If Exists

Using SQL Server I have needed to drop functions and stored procedures before creating them in a script. Today I had to drop a table. After searching around a bit I found that I could test if the drop was necessary using much the same syntax as the others.

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

The type of the object is U for USER_TABLE.