How do I drop a function if it already exists?

Question

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 1

if object_id('FUNCTION_NAME') is not NULL
   DROP FUNCTION <name>

You can also look the name up in sysobjects

IF EXISTS (SELECT * 
       FROM   sysobjects 
           WHERE name='<function name>' and xtype='FN'

Actually, if the function could be a table function, you need to use

xtype in ('FN','TF')

Answer 2

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

Answer 3

I usually shy away from queries from sys* type tables, vendors tend to change these between releases, major or otherwise. What I have always done is to issue the DROP FUNCTION <name> statement and not worry about any SQL error that might come back. I consider that standard procedure in the DBA realm.

Answer 4

IF EXISTS 
(SELECT * FROM sys.objects 
WHERE object_id = OBJECT_ID(N'functionName') 
AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))

DROP FUNCTION functionName
GO