Droping tables in T-SQL

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))
RETURNS bit
AS
BEGIN

DECLARE @OUT bit

IF EXISTS(SELECT TABLE_NAME FROM DATA.INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = LTRIM(RTRIM(@tablename)))
SET @OUT = 1
ELSE
SET @OUT = 0

RETURN @OUT
END

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.

Comments are closed.