Skip Navigation

SQL If Exists Then Drop


This is one topic that I continually find myself refering to my notes and bookmarks on and I've finally decided to add this piece of very good reference information to my own blog.

Tables

Option 1:

IF OBJECT_ID('enterTableNameHere', 'U') IS NOT NULL
BEGIN
DROP TABLE [dbo].[enterTableNameHere]
END
GO

Option 2:

IF EXISTS
(
SELECT * FROM dbo.sysobjects
WHERE id = object_id(N'[dbo].[enterTableNameHere]')
AND OBJECTPROPERTY(id, N'IsUserTable') = 1
)
BEGIN
DROP TABLE [dbo].[enterTableNameHere]
END
GO

Views

IF EXISTS
(
SELECT * FROM INFORMATION_SCHEMA.VIEWS
WHERE TABLE_SCHEMA = 'enterSchemaNameHere' AND TABLE_NAME = 'enterViewNameHere'
)
BEGIN
DROP VIEW [dbo].[enterViewNameHere]
END
GO

Stored Procedures

IF EXISTS
(
SELECT * FROM dbo.sysobjects
WHERE id = object_id(N'[dbo].[enterStoredProcedureNameHere]')
AND OBJECTPROPERTY(id, N'IsProcedure') = 1
)
BEGIN
DROP PROCEDURE [dbo].[enterStoredProcedureNameHere]
END
GO

User-Defined Functions

IF EXISTS
(
SELECT * FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_NAME = 'enterFunctionNameHere'
AND ROUTINE_SCHEMA = 'dbo'
AND ROUTINE_TYPE = 'FUNCTION'
)
BEGIN
DROP FUNCTION [dbo].[enterFunctionNameHere]
END
GO