Tuesday, October 21, 2014
   
Text Size
Login

Drop If Object Exists

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.

  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 
  5.   
  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 
  10.   
  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 
  15.   
  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)

  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 
Add Comment

Name:

Email:

Website:

Message:


Latest Posts

  • Convert to Proper Case in T-SQL

    • Tue 07-Oct-14
      Really comprehensive function. Nice work! Dave.
      starsky51
  • Joes Quicklist Weblinks (JQW)

    • Wed 08-Oct-14
      Thanks jazzmang! This module is due for an update so I'll review this issue in the next version.
      Webmaster  
    • Tue 07-Oct-14
      I've set "Display Images" under Module Settings but the modules continues to display a hard coded CSS ...
      jazzmang
  • Joes Word Cloud (JWC)

    • Thu 16-Oct-14
      hello i use your tag cloud component in my joomla 2.5 website. i'm french and there is probleme with ...
      plykite2010  
    • Tue 07-Oct-14
      Hello, i have Joomla 2.5.27 and Module JWC v3.2.2 installed. In Configuration i fill in how many Word ...
      Maic