Ideally, we want to have a function that will drop a table if it exists, do nothing if it doesn’t exist. This can be done in T-SQL or DTS. In DTS we create a single DROP TABLE statement into SQL task and then have the exit arrow set to ‘on completion’ so the code goes on even if the table didn’t exist. This can create a lot of clutter on the DTS drawing board.
Writing pure T-SQL makes this a pain, but with a T-SQL function, at least you can shorten the code.
IF dbo.TableExists('myTable')=1 -- THEN
drop table myTable
-- END IF
The DDL for the above function is :
FUNCTION dbo.TableExists(@tableName varchar(250))
DECLARE @OUT bit
IF EXISTS(SELECT TABLE_NAME FROM DATA.INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = LTRIM(RTRIM(@tablename)))
SET @OUT = 1
SET @OUT = 0
The database name needs to be changed for each database where the function is deployed. Also, notice I’m returning a bit and not a boolean, (which I’m not sure if it is possible), so you have to check for equality to 1 or 0 when using the function.