如何检查函数是否存在于 SQL 数据库中
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5419082/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to check if a function exists on a SQL database
提问by Dr. Greenthumb
I need to find out if a function exists on a database, so that I can drop it and create it again. It should basically be something like the following code that I use for stored procedures:
我需要找出一个函数是否存在于数据库中，以便我可以删除它并重新创建它。它基本上应该类似于我用于存储过程的以下代码：
IF EXISTS (
SELECT *
FROM dbo.sysobjects
WHERE id = OBJECT_ID(N'[dbo].[SP_TEST]')
AND OBJECTPROPERTY(id, N'IsProcedure') = 1 )
回答by Martin Smith
This is what SSMS uses when you script using the DROP and CREATEoption
这是 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
This approach to deploying changes means that you need to recreate all permissions on the object so you might consider ALTER-ing if Exists instead.
这种部署更改的方法意味着您需要重新创建对象的所有权限，因此您可以考虑ALTER-ing if Exists。
回答by Law Metzler
I tend to use the Information_Schema:
我倾向于使用 Information_Schema：
IF EXISTS ( SELECT 1
FROM Information_schema.Routines
WHERE Specific_schema = 'dbo'
AND specific_name = 'Foo'
AND Routine_Type = 'FUNCTION' )
for functions, and change Routine_Typefor stored procedures
用于函数和更改Routine_Type存储过程
IF EXISTS ( SELECT 1
FROM Information_schema.Routines
WHERE Specific_schema = 'dbo'
AND specific_name = 'Foo'
AND Routine_Type = 'PROCEDURE' )
回答by Kapé
Why not just:
为什么不只是：
IF object_id('YourFunctionName', 'FN') IS NOT NULL
BEGIN
DROP FUNCTION [dbo].[YourFunctionName]
END
GO
The second argument of object_idis optional, but can help to identify the correct object. There are numerous possible valuesfor this type argument, particularly:
的第二个参数object_id是可选的，但可以帮助识别正确的对象。此类型参数有许多可能的值，特别是：
- FN : Scalar function
- IF : Inline table-valued function
- TF : Table-valued-function
- FS : Assembly (CLR) scalar-function
- FT : Assembly (CLR) table-valued function
- FN : 标量函数
- IF : 内联表值函数
- TF : 表值函数
- FS：汇编（CLR）标量函数
- FT：汇编 (CLR) 表值函数
回答by Jeremy
I've found you can use a very non verbose and straightforward approach to checking for the existence various SQL Server objects this way:
我发现您可以使用一种非常不冗长且直接的方法来检查各种 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
This is based on the OBJECTPROPERTY function which is available in SQL 2005+. The MSDN article can be found here.
这是基于 SQL 2005+ 中可用的 OBJECTPROPERTY 函数。可以在此处找到 MSDN 文章。
The OBJECTPROPERTY function uses the following signature:
OBJECTPROPERTY 函数使用以下签名：
OBJECTPROPERTY ( id , property )
You pass a literal value into the property parameter, designating the type of object you are looking for. There's a massive list of values you can supply.
您将文字值传递给属性参数，指定您要查找的对象类型。您可以提供大量的值。
回答by jamiedanq
I know this thread is old but I just wanted to add this answer for those who believe it's safer to Alterthan Dropand Create. The below will Alterthe Functionif it exists or Createit if doesn't:
我知道这个线程很旧，但我只是想为那些认为Alter比Dropand更安全的人添加这个答案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
...
