Tutorial :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


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


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  


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  


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.


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


DROP Function IF EXISTS udf_name  

More info here


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;  


Check IF Exist For Function

 IF  EXISTS (SELECT TOP 1 1 FROM sys.objects WHERE           object_id = OBJECT_ID(N'[Schema].[function_Name]')           AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))  BEGIN  DROP FUNCTION [Schema].[function_Name]  Print('function dropped => [Schema].[function_Name]')  END  GO  

Check IF Exist for Stored procedure , Function also by clicking below link http://www.gurujipoint.com/2017/05/check-if-exist-for-trigger-function-and.html


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


Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Next Post »