Drop If Object Exists

: Joel Lipman; Transact-SQL; 03 August 2012; Hits: 15495

What?
Some people have been suggesting you can use "CREATE OR REPLACE ... VIEW ... FUNCTION" but my SQL Server 2008 Management Studio doesn't like this and refuses to understand what I'm trying to do.

Why?
As this data seemed to be across various websites, I wanted a page which has all of them in one place. So here you go, I hope it's of some use. If it's wrong then just post a comment at the bottom of this page.

Go go go
Note that in the following examples, I'm checking under the [Common] schema, this might be [dbo] for you or a more specific one.

copy raw

-- drop a stored procedure if it exists
IF OBJECT_ID ( '[Common].[usp_MyStoredProcedure]', 'P' ) IS NOT NULL 
    DROP PROCEDURE [Common].[usp_MyStoredProcedure];
GO

-- drop a view if it exists
IF OBJECT_ID ( '[Common].[uvw_MyView]', 'V' ) IS NOT NULL 
    DROP VIEW [Common].[uvw_MyView];
GO

-- drop a function if it exists
IF OBJECT_ID ( '[Common].[ufn_MyFunction]', 'FN' ) IS NOT NULL 
    DROP FUNCTION [Common].[ufn_MyFunction];
GO

-- drop a user table if it exists
IF OBJECT_ID ( '[dbo].[myUserTable]', 'U' ) IS NOT NULL 
    DROP TABLE [dbo].[myUserTable];
GO

-- drop a stored procedure if it exists
IF OBJECT_ID ( '[Common].[usp_MyStoredProcedure]', 'P' ) IS NOT NULL
DROP PROCEDURE [Common].[usp_MyStoredProcedure];
GO
-- drop a view if it exists
IF OBJECT_ID ( '[Common].[uvw_MyView]', 'V' ) IS NOT NULL
DROP VIEW [Common].[uvw_MyView];
GO
-- drop a function if it exists
IF OBJECT_ID ( '[Common].[ufn_MyFunction]', 'FN' ) IS NOT NULL
DROP FUNCTION [Common].[ufn_MyFunction];
GO
-- drop a user table if it exists
IF OBJECT_ID ( '[dbo].[myUserTable]', 'U' ) IS NOT NULL
DROP TABLE [dbo].[myUserTable];
GO

Types for sys.objects (less used by me)

copy raw

AF	= Aggregate function (CLR)
C	= CHECK constraint
D	= DEFAULT (constraint or stand-alone)
F	= FOREIGN KEY constraint
FN	= SQL scalar function
FS	= Assembly (CLR) scalar-function
FT	= Assembly (CLR) table-valued function
IF	= SQL inline table-valued function
IT	= Internal table
P	= SQL Stored Procedure
PC	= Assembly (CLR) stored-procedure
PG	= Plan guide
PK	= PRIMARY KEY constraint
R	= Rule (old-style, stand-alone)
RF	= Replication-filter-procedure
S	= System base table
SN	= Synonym
SQ	= Service queue
TA	= Assembly (CLR) DML trigger
TF	= SQL table-valued-function
TR	= SQL DML trigger
TT	= Table type
U	= Table (user-defined)
UQ	= UNIQUE constraint
V	= View
X	= Extended stored procedure