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

User · Answer

IF EXISTS        SELECT          FROM schema sys objects       WHERE name    func name       DROP FUNCTION  dbo   func name  GO

User · Answer

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

User · Answer

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

User · Answer

if object id  FUNCTION NAME   is not NULL    DROP FUNCTION  lt name gt    You can also look the name up in sysobjects  IF EXISTS  SELECT           FROM   sysobjects             WHERE name   lt function name gt   and xtype  FN    Actually  if the function could be a table function  you need to use  xtype in   FN   TF

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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  lt name gt  statement and not worry about any SQL error that might come back   I consider that standard procedure in the DBA realm

User · Answer

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

User · Answer

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

[tsql] How do I drop a function if it already exists?

Examples related to tsql

Examples related to sql-server-2000