# How do I drop a function if it already exists?

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.

``````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
• Hey thanks, I didn't know Object_id had a second parameter for the type of object Feb 18 '10 at 18:11
• 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
``````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')
``````
• 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... Feb 18 '10 at 18:57

This works for any object, not just functions:

``````IF OBJECT_ID('YourObjectName') IS NOT NULL
``````

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

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
``````
``````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
``````

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
``````

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.

• 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
``````IF EXISTS
(SELECT *
FROM schema.sys.objects
WHERE name = 'func_name')
DROP FUNCTION [dbo].[func_name]
GO
``````

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;
``````

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
``````
{"ft_lang_label":"__label__en","ft_lang_prob":0.70997804,"math_prob":0.5589934,"size":3656,"snap":"2021-31-2021-39","text_gpt3_token_len":1004,"char_repetition_ratio":0.13417305,"word_repetition_ratio":0.049152542,"special_character_ratio":0.2866521,"punctuation_ratio":0.1408046,"nsfw_num_words":0,"has_unicode_error":false,"math_prob_llama3":0.96404487,"pos_list":[0],"im_url_duplicate_count":[null],"WARC_HEADER":"{\"WARC-Type\":\"response\",\"WARC-Date\":\"2021-09-18T17:16:16Z\",\"WARC-Record-ID\":\"<urn:uuid:81b7e941-a03b-43bf-a365-a4ea584cc73a>\",\"Content-Length\":\"249719\",\"Content-Type\":\"application/http; msgtype=response\",\"WARC-Warcinfo-ID\":\"<urn:uuid:c2a02c8e-45b2-4739-a193-186e5d35b876>\",\"WARC-Concurrent-To\":\"<urn:uuid:ab3fdf73-eecf-4876-b68b-6c77b9510951>\",\"WARC-IP-Address\":\"151.101.193.69\",\"WARC-Target-URI\":\"https://stackoverflow.com/questions/2290770/how-do-i-drop-a-function-if-it-already-exists/2290815\",\"WARC-Payload-Digest\":\"sha1:QQE4VODJSRJ5FWK4OYEIP5PZ7DAISQ4B\",\"WARC-Block-Digest\":\"sha1:HPH5XATBREQ3MLF7GBSJHGE2SEAX74GJ\",\"WARC-Identified-Payload-Type\":\"text/html\",\"warc_filename\":\"/cc_download/warc_2021/CC-MAIN-2021-39/CC-MAIN-2021-39_segments_1631780056548.77_warc_CC-MAIN-20210918154248-20210918184248-00691.warc.gz\"}"}