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.

1 comment:

blair said...

Thanks for this, that is for reassuring me that I'm not missing a simple command. This is literally the first line of script I tried to convert to T-sql.

The version from the MSoft example about temporary tables, is a little simpler (with my table name):
IF OBJECT_ID(N'dbo.batch_dates', N'U') IS NOT NULL
DROP TABLE dbo.batch_dates;