Drop If Object Exists

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.

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.
-- drop a stored procedure if it exists
IF OBJECT_ID ( '[Common].[usp_MyStoredProcedure]', 'P' ) IS NOT NULL 
    DROP PROCEDURE [Common].[usp_MyStoredProcedure];

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

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

-- drop a user table if it exists
IF OBJECT_ID ( '[dbo].[myUserTable]', 'U' ) IS NOT NULL 
    DROP TABLE [dbo].[myUserTable];
  1.  -- drop a stored procedure if it exists 
  2.  IF OBJECT_ID ( '[Common].[usp_MyStoredProcedure]', 'P' ) IS NOT NULL 
  3.      DROP PROCEDURE [Common].[usp_MyStoredProcedure]
  4.  GO 
  6.  -- drop a view if it exists 
  7.  IF OBJECT_ID ( '[Common].[uvw_MyView]', 'V' ) IS NOT NULL 
  8.      DROP VIEW [Common].[uvw_MyView]
  9.  GO 
  11.  -- drop a function if it exists 
  12.  IF OBJECT_ID ( '[Common].[ufn_MyFunction]', 'FN' ) IS NOT NULL 
  13.      DROP FUNCTION [Common].[ufn_MyFunction]
  14.  GO 
  16.  -- drop a user table if it exists 
  17.  IF OBJECT_ID ( '[dbo].[myUserTable]', 'U' ) IS NOT NULL 
  18.      DROP TABLE [dbo].[myUserTable]
  19.  GO 

Types for sys.objects (less used by me)
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
  1.  AF    = Aggregate function (CLR) 
  2.  C    = CHECK constraint 
  3.  D    = DEFAULT (constraint or stand-alone) 
  4.  F    = FOREIGN KEY constraint 
  5.  FN    = SQL scalar function 
  6.  FS    = Assembly (CLR) scalar-function 
  7.  FT    = Assembly (CLR) table-valued function 
  8.  IF    = SQL inline table-valued function 
  9.  IT    = Internal table 
  10.  P    = SQL Stored Procedure 
  11.  PC    = Assembly (CLR) stored-procedure 
  12.  PG    = Plan guide 
  13.  PK    = PRIMARY KEY constraint 
  14.  R    = Rule (old-style, stand-alone) 
  15.  RF    = Replication-filter-procedure 
  16.  S    = System base table 
  17.  SN    = Synonym 
  18.  SQ    = Service queue 
  19.  TA    = Assembly (CLR) DML trigger 
  20.  TF    = SQL table-valued-function 
  21.  TR    = SQL DML trigger 
  22.  TT    = Table type 
  23.  U    = Table (user-defined) 
  24.  UQ    = UNIQUE constraint 
  25.  V    = View 
  26.  X    = Extended stored procedure 
Category: Transact-SQL :: Article: 423

Credit where Credit is Due:

Feel free to copy, redistribute and share this information. All that we ask is that you attribute credit and possibly even a link back to this website as it really helps in our search engine rankings.

Disclaimer: The information on this website is provided without warranty and any content is merely the opinion of the author. Please try to test in development environments prior to adapting them to your production environments. The articles are written in good faith and, at the time of print, are working examples used in a commercial setting.

Thank you for visiting and, as always, we hope this website was of some use to you!

Kind Regards,

Joel Lipman

Related Articles

Joes Revolver Map

Joes Word Cloud


Badge - Certified Zoho Creator Associate
Badge - Certified Zoho Creator Associate

Donate & Support

If you like my content, and would like to support this sharing site, feel free to donate using a method below:

Donate to Joel Lipman via PayPal

Donate to Joel Lipman with Bitcoin - Valid till 8 May 2022 bc1qjtp4l4ra452wzvuk9a45yfj82zkahsyy2z379y
© 2022 Joel Lipman .com. All Rights Reserved.