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

Question

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

IF EXISTS (
     SELECT  *
     FROM    dbo.sysobjects
     WHERE   id = OBJECT_ID(N'[dbo].[SP_TEST]')
             AND OBJECTPROPERTY(id, N'IsProcedure') = 1 )

- Dr. Greenthumb

Answer 1

使用DROP和CREATE选项进行脚本撰写时，SSMS所使用的内容。

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。

- Martin Smith

---

让我更加好奇的是为什么没有一个sys.functions系统目录视图..... - marc_s

Answer 2

我倾向于使用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' ) 

- Law Metzler

---

很酷，我一直在寻找这样的东西，但从未找到。我认为通常最好使用information_schema，因为它不与特定的RDBMS绑定。（顺便说一下，跨平台兼容性的概念来自于这个答案：https://dev59.com/Amsz5IYBdhLWcg3wcnhb#14290099） - user420667

Answer 3

为什么不这样做：

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

object_id 的第二个参数是可选的，但有助于标识正确的对象。对于此类型参数，有许多可能的值，特别是：

• FN：标量函数
• IF：内联表值函数
• TF：表值函数
• FS：程序集 (CLR) 标量函数
• FT：程序集 (CLR) 表值函数
• U：表 (用户定义)

- Kapé

---

从技术上讲，这可能会失败，因为它仅检查是否存在具有该名称的对象，而不是检查该对象是否为函数。例如，如果运行代码时输入“CREATE TABLE YourFunctionName(X INT);”，则会失败。 - Martin Smith

@MartinSmith：很容易使其更加健壮。只需使用 object_id('YourFunction', 'FN') 或任何其他清晰表明您所引用的对象类型的指示符（第二个参数）即可。 - darlove

@darlove 使用“FN”作为第二个参数可能不起作用。我刚学到，“FN”表示标量函数。这个链接告诉你可以传递的不同参数值http://sqlhints.com/tag/how-to-check-if-function-exists/。我一直在使用“FN”来检查现有的表值函数，但它不起作用。我必须使用“TF”。 - user12345

Answer 4

我知道这个帖子很旧，但是我想为那些认为修改比删除和创建更安全的人添加此答案。以下内容将会修改此函数（如果存在）或者创建它（如果不存在）：

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

- jamiedanq

---

我喜欢这个，但是我认为应该是"ALTER FUNCTION"，不是吗？ - Erik

我喜欢 ALTER OR CREATE。 - AgentFire

Answer 5

我发现你可以使用一种非常简洁直接的方法来检查各种SQL Server对象是否存在，如下所示：

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

这是基于可在SQL 2005+中使用的OBJECTPROPERTY函数。MSDN文章可以在这里找到。

OBJECTPROPERTY函数的签名如下：

OBJECTPROPERTY ( id , property ) 

您需要将字面值传递到属性参数中，以指定您要查找的对象类型。可以提供大量可用的值。

- Jeremy

---

如果包含一个完整的if/drop示例，我认为更容易看到这个答案的简单性。 - Jonathan

Answer 6

从SQL Server 2016 SP1开始，SQL Server 2017或更高版本，您可以使用以下语法：

CREATE OR ALTER [object] ...

为了避免这些麻烦。这样做还有一个额外的好处，即关于过程的元数据被持久化（而不是每次更改都获得一个全新的对象ID），有时非常有用，特别是如果你使用像QueryStore或其他关心对象ID的工具。

- TZHX