如何检查 SQL 数据库中是否存在函数？

我需要找出数据库中是否存在一个函数，这样我就可以删除它并重新创建它。它基本上应该类似于我用于存储过程的以下代码:

IF EXISTS (
SELECT  *
FROM    dbo.sysobjects
WHERE   id = OBJECT_ID(N'[dbo].[SP_TEST]')
AND OBJECTPROPERTY(id, N'IsProcedure') = 1 )
177638 次浏览
小开
最佳答案

这是 SSMS 在使用 DROP and CREATE选项编写脚本时所使用的

IF EXISTS (SELECT *
FROM   sys.objects
WHERE  object_id = OBJECT_ID(N'[dbo].[foo]')
AND type IN ( N'FN', N'IF', N'TF', N'FS', N'FT' ))
DROP FUNCTION [dbo].[foo]


GO

这种部署更改的方法意味着您需要重新创建对象的所有权限，以便在存在时可以考虑 ALTER-ing。

小开

我倾向于使用 Information _ Schema:

IF EXISTS ( SELECT  1
FROM    Information_schema.Routines
WHERE   Specific_schema = 'dbo'
AND specific_name = 'Foo'
AND Routine_Type = 'FUNCTION' )

函数，并更改存储过程的 Routine_Type

IF EXISTS ( SELECT  1
FROM    Information_schema.Routines
WHERE   Specific_schema = 'dbo'
AND specific_name = 'Foo'
AND Routine_Type = 'PROCEDURE' )
小开

我发现您可以使用一种非常简单明了的方法来检查各种 SQLServer 对象是否存在:

IF OBJECTPROPERTY (object_id('schemaname.scalarfuncname'), 'IsScalarFunction') = 1
IF OBJECTPROPERTY (object_id('schemaname.tablefuncname'), 'IsTableFunction') = 1
IF OBJECTPROPERTY (object_id('schemaname.procname'), 'IsProcedure') = 1

这是基于 SQL2005 + 中提供的 ObjecCTPROPERTY 函数。可以在 给你中找到 MSDN 文章。

ObjectPROPERTY 函数使用以下签名:

OBJECTPROPERTY ( id , property )

将一个文本值传递到 property 参数中，指定要查找的对象的类型。你可以提供大量的价值观。

小开

为什么不只是:

IF object_id('YourFunctionName', 'FN') IS NOT NULL
BEGIN
DROP FUNCTION [dbo].[YourFunctionName]
END
GO

object_id的第二个参数是可选的，但是可以帮助识别正确的对象。这个类型参数有 许多可能的价值观，特别是:

  • FN: 标量函数
  • IF: 内联表值函数
  • TF: 表值函数
  • FS: Assembly (CLR)标量函数
  • 组装(CLR)表值函数
  • 表(用户定义的)
小开

我知道这个线程是旧的，但我只是想添加这个答案，为那些谁认为它是安全的 Alter比 Drop和 Create。下面将 Alter的 Function如果它存在或 Create它如果不存在:

  IF NOT EXISTS (SELECT *
FROM   sys.objects
WHERE  object_id = OBJECT_ID(N'[dbo].[foo]')
AND type IN ( N'FN', N'IF', N'TF', N'FS', N'FT' ))
EXEC('CREATE FUNCTION [dbo].[foo]() RETURNS INT AS BEGIN RETURN 0 END')
GO
ALTER FUNCTION [dbo].[foo]
AS
...
小开

在 SQLServer2016SP1、 SQLServer2017或更高版本中，可以使用以下语法:

CREATE OR ALTER [object] ...

为了避免这些麻烦。这还有一个关于持久化过程的元数据的额外好处(而不是每次更改它都会得到一个全新的 objectid) ，这有时会很有用，特别是当您使用 QueryStore 或其他关心 objectid 的工具时。


提交答案