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.

Drawbridge

Answer 1

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

Fuller

Hey thanks, I didn't know Object_id had a second parameter for the type of object – Gland

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... – Dissenter

Answer 2

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

Gland

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... – Gland

Answer 3

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   

Eusporangiate

Answer 4

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

Florella

Answer 5

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

Judges

Answer 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

Disjoint

Answer 7

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.

Branks

sys. in SQL Server 2005 is the official way. They are views not tables nowadays and the actual sys tables are hidden from us. – Dissenter

Answer 8

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

Alyworth

Answer 9

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;

Roselba

Answer 10

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

Chenab