How do I drop a function if it already exists?

tsql sql-server-2000
DavidStein picture DavidStein · Feb 18, 2010 · Viewed 128.2k times · Source

I know this must be simple, but how do I preface the creation of a function with a check to see if it already exists? If it exists, I want to drop and re-create it.

Answer

adrianbanks picture adrianbanks · Feb 18, 2010 
IF EXISTS (
    SELECT * FROM sysobjects WHERE id = object_id(N'function_name') 
    AND xtype IN (N'FN', N'IF', N'TF')
)
    DROP FUNCTION function_name
GO

If you want to avoid the sys* tables, you could instead do (from here in example A):

IF object_id(N'function_name', N'FN') IS NOT NULL
    DROP FUNCTION function_name
GO

The main thing to catch is what type of function you are trying to delete (denoted in the top sql by FN, IF and TF):

  • FN = Scalar Function
  • IF = Inlined Table Function
  • TF = Table Function

Related questions

Check if table exists in SQL Server

I would like this to be the ultimate discussion on how to check if a table exists in SQL Server 2000/2005 using SQL Statements. When you Google for the answer, you get so many different answers. Is there an official/backward …

Read More
How to Join to first row

I'll use a concrete, but hypothetical, example. Each Order normally has only one line item: Orders: OrderGUID OrderNumber ========= ============ {FFB2...} STL-7442-1 {3EC6...} MPT-9931-8A LineItems: LineItemGUID Order ID Quantity Description ============ ======== ======== ================================= {098FBE3...} 1 7 prefabulated amulite {1609B09...} 2 32 spurving bearing But occasionally there …

Read More
How do I generate random number for each row in a TSQL Select?

I need a different random number for each row in my table. The following seemingly obvious code uses the same random value for each row. SELECT table_name, RAND() magic_number FROM information_schema.tables I'd like to get an …

Read More