如果函数已经存在，我该如何删除它？

我知道这一定很简单，但是如何在创建函数前面加上一个检查来查看它是否已经存在？如果它存在，我想删除并重新创建它。

145866 次浏览
小开 
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')
小开
最佳答案 
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
小开

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 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
小开

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
小开 
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 ]

Query:

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;
小开

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

提交答案