Why/How To Drop SQL Entities If They Exist?

Sometimes while writing SQL scripts purposed for fixing production issues you face some unexpected problems. These problems may be due to the existance of discrepancy between client's environments caused by missing some scripts and may be by other reasons.

In these cases you have to make sure that your scripts are somehow adaptable and can be run on all environments without raising errors and without causing any unexpected behaviors. These errors like trying to update a stored procedure which doesn't exist and so on.

The best practice in this situation which works perfectly most of the times is to first drop the entity you wish to modify if it already exists and then fully re-create it as you wish. This allows you to avoid many unexpected issues you can face.

So, below you will find the code which checks for the existance of some entities and accordingly drops these entities.


Tables

--Check if table exists in a database
IF EXISTS
(
 SELECT *
 FROM dbo.sysobjects
 WHERE id = object_id(N'[dbo].[TableName]')
 AND OBJECTPROPERTY(id, N'IsUserTable') = 1
)

DROP TABLE [dbo].[TableName]
GO

Views

--Check if view exists in a database
IF EXISTS
(
 SELECT *
 FROM INFORMATION_SCHEMA.VIEWS
 WHERE TABLE_NAME = 'ViewName'
)

DROP VIEW [dbo].[ViewName]
GO

Stored Procedures

--Check if stored procedure exists in a database
IF EXISTS
(
 SELECT *
 FROM dbo.sysobjects
 WHERE id = object_id(N'[dbo].[StoredProcedureName]')
 AND OBJECTPROPERTY(id, N'IsProcedure') = 1
)

DROP PROCEDURE [dbo].[StoredProcedureName]
GO

User-defined Functions

--Check if user-defined function exists in a database
IF EXISTS
(
 SELECT  *
 FROM INFORMATION_SCHEMA.ROUTINES
 WHERE ROUTINE_NAME = 'FunctionName'
 AND ROUTINE_SCHEMA = 'dbo'
 AND ROUTINE_TYPE = 'FUNCTION'
)

DROP FUNCTION [dbo].[FunctionName]
GO

User-defined Table Types

--Check if user-defined table type exists in a database
IF EXISTS
(
 SELECT * 
 FROM sys.types 
 WHERE is_table_type = 1 
 AND name = 'TableTypeName'
)

DROP TYPE [dbo].[TableTypeName]
GO

Table Indexes

--Check if table index exists in a database
IF EXISTS
(
 SELECT * 
 FROM sys.indexes 
 WHERE name='IndexName'
 AND object_id = OBJECT_ID('TableName')
)

DROP INDEX [IndexName] ON [dbo].[TableName]
GO

Hope you find this useful.