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

Question

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

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

Answer 1

这就是 SSMS 在使用 DROP 和 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 if Exists 代替。

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

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 : 表（用户定义）

Answer 4

我知道这个线程很旧，但我只是想为那些认为 Alter 比 Drop 和 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
  ...

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 ) 

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

Answer 6

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

CREATE OR ALTER [object] ...

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