如果函数已经存在，如何删除它?

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.

我知道这一定很简单，但是我如何在一个函数的创建之前先检查它是否已经存在?如果它存在，我想放弃并重新创建它。

11 个解决方案

#1

154

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

如果你想避免sys*表，你可以做(从这里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):

要捕获的主要内容是您要删除的函数类型(如果和TF，在顶部sql中表示):

FN = Scalar Function
FN =标量函数
IF = Inlined Table Function
IF =内联表函数。
TF = Table Function
TF =表函数

#2

if object_id('FUNCTION_NAME') is not NULL
   DROP FUNCTION <name>

You can also look the name up in sysobjects

您也可以在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')

#3

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
GO

#4

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

#5

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

您有两个选项可以在SQL Server 2016中删除和重新创建过程。

Starting from SQL Server 2016 - use "IF EXISTS"

从2016年的SQL Server开始——使用“如果存在”

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

Starting from SQL Server 2016 SP1 - use "OR ALTER"

从SQL Server 2016 SP1 - use“或ALTER”开始

CREATE [ OR ALTER ] FUNCTION [ schema_name. ] function_name

#6

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*类型表的查询，供应商倾向于在发行版、专业版或其他版本之间更改这些查询。我一直做的是发布DROP函数语句，不担心可能会出现的任何SQL错误。我认为这是DBA领域的标准程序。

#7

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

#8

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

从SQL Server 2016 CTP3中，可以使用新的DIE语句，而不是大的IF包装器。

Syntax :

语法:

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

DROP函数[如果存在]{[schema_name]。]function_name}[，…n]

Query:

查询:

DROP Function IF EXISTS udf_name

More info here

更多的信息在这里

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

#10

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

检查是否存在存储过程，也可以点击下面的链接http://www.gurujipoint.com/2017/05/checkif - Exist -for trigger- Function -and.html。

#11

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

如果您想使用SQL ISO标准INFORMATION_SCHEMA而不是SQL Server-specific sysobjects，您可以这样做:

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

#1

154

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