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.

adrianbanks · Accepted Answer · 2010-02-18T17:46:57.070

204

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

edited Feb 18 '10 at 17:46

answered Feb 18 '10 at 17:41

adrianbanks

81,306
22
176
206

Hey thanks, I didn't know Object_id had a second parameter for the type of object – Sparky Feb 18 '10 at 18:11
1

given object names (that appear in sys.objects) have to be unique, querying xtype is redundant. Try creating a table and a stored proc with the same name... – gbn Feb 18 '10 at 18:21

score 24 · Answer 2 · answered Feb 18 '10 at 17:39

24

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')

answered Feb 18 '10 at 17:39

Sparky

14,967
2
31
45

2

I've always preferred the Object_id method, it seems simpler to read in the code. Always curious why the Microsoft generated sample code uses the sys.objects lookup instead... – Sparky Feb 18 '10 at 18:57

score 16 · Answer 3 · edited Nov 05 '19 at 22:05

16

You have two options to drop and recreate the procedure in SQL Server 2016.

Starting from SQL Server 2016 - use IF EXISTS

DROP FUNCTION [ IF EXISTS ] { [ schema_name. ] function_name } [ ,...n ]   [;]

Starting from SQL Server 2016 SP1 - use OR ALTER

CREATE [ OR ALTER ] FUNCTION [ schema_name. ] function_name

edited Nov 05 '19 at 22:05

KyleMit

30,350
66
462
664

answered Dec 13 '16 at 07:47

Konrad

380
2
5

Metaphor · Answer 4 · 2018-09-17T18:53:15.433

15

This works for any object, not just functions:

IF OBJECT_ID('YourObjectName') IS NOT NULL

then just add your flavor of object, as in:

IF OBJECT_ID('YourFunction') IS NOT NULL
   DROP FUNCTION YourFunction

edited Sep 17 '18 at 18:53

answered Sep 18 '13 at 13:25

Metaphor

6,157
10
54
77

score 6 · Answer 5 · answered Feb 18 '10 at 17:46

6

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

answered Feb 18 '10 at 17:46

Fiona - myaccessible.website

14,481
16
82
117

Pரதீப் · Answer 6 · 2015-11-22T15:04:19.337

6

From SQL Server 2016 CTP3 you can use new DIE statements instead of big IF wrappers

Syntax :

DROP FUNCTION [ IF EXISTS ] { [ schema_name. ] function_name } [ ,...n ]

Query:

DROP Function IF EXISTS udf_name

More info here

edited Nov 22 '15 at 15:04

answered Nov 22 '15 at 14:26

Pரதீப்

91,748
19
131
172

score 2 · Answer 7 · answered Feb 18 '10 at 17:42

2

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.

answered Feb 18 '10 at 17:42

Jeff Walker

1,656
1
18
36

1

sys. in SQL Server 2005 is the official way. They are *views* not tables nowadays and the actual sys tables are hidden from us. – gbn Feb 18 '10 at 18:22

score 0 · Answer 8 · edited May 01 '15 at 15:37

0

IF EXISTS
      (SELECT * 
      FROM schema.sys.objects
      WHERE name = 'func_name')
    DROP FUNCTION [dbo].[func_name]
GO

edited May 01 '15 at 15:37

TZHX

5,291
15
47
56

answered May 01 '15 at 15:14

user2419223

1

score 0 · Answer 9 · answered Jun 10 '16 at 19:59

Here's my take on this:

if(object_id(N'[dbo].[fn_Nth_Pos]', N'FN')) is not null
    drop function [dbo].[fn_Nth_Pos];
GO
CREATE FUNCTION [dbo].[fn_Nth_Pos]
(
    @find char, --char to find
    @search varchar(max), --string to process   
    @nth int --occurrence   
)
RETURNS int
AS
BEGIN
    declare @pos int --position of nth occurrence
    --init
    set @pos = 0

    while(@nth > 0)
    begin       
        set @pos = charindex(@find,@search,@pos+1)
        set @nth = @nth - 1
    end 

    return @pos
END
GO

--EXAMPLE
declare @files table(name varchar(max));

insert into @files(name) values('abc_1_2_3_4.gif');
insert into @files(name) values('zzz_12_3_3_45.gif');

select
    f.name,
    dbo.fn_Nth_Pos('_', f.name, 1) as [1st],
    dbo.fn_Nth_Pos('_', f.name, 2) as [2nd],
    dbo.fn_Nth_Pos('_', f.name, 3) as [3rd],
    dbo.fn_Nth_Pos('_', f.name, 4) as [4th]
from 
    @files f;

score 0 · Answer 10 · answered Dec 14 '17 at 16:45

If you want to use the SQL ISO standard INFORMATION_SCHEMA and not the SQL Server-specific sysobjects, you can do this:

IF EXISTS (
    SELECT ROUTINE_NAME FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME = N'FunctionName'
)
   DROP FUNCTION [dbo].[FunctionName]
GO

How do I drop a function if it already exists?

10 Answers10