How to check if a function exists in a SQL database?
SqlSql ServerSql FunctionSql Problem Overview
I need to find out if a function exists in 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 )
Sql Solutions
Solution 1 - Sql
This is what SSMS uses when you script using the DROP and CREATE
option
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.
Solution 2 - Sql
I tend to use the 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_Type
for stored procedures
IF EXISTS ( SELECT 1
FROM Information_schema.Routines
WHERE Specific_schema = 'dbo'
AND specific_name = 'Foo'
AND Routine_Type = 'PROCEDURE' )
Solution 3 - Sql
Why not just:
IF object_id('YourFunctionName', 'FN') IS NOT NULL
BEGIN
DROP FUNCTION [dbo].[YourFunctionName]
END
GO
The second argument of object_id
is optional, but can help to identify the correct object. There are numerous possible values for this type argument, particularly:
- FN : Scalar function
- IF : Inline table-valued function
- TF : Table-valued-function
- FS : Assembly (CLR) scalar-function
- FT : Assembly (CLR) table-valued function
Solution 4 - Sql
I've found you can use a very non verbose and straightforward approach to checking for the existence various SQL Server objects this way:
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.
The OBJECTPROPERTY function uses the following signature:
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.
Solution 5 - Sql
I know this thread is old but I just wanted to add this answer for those who believe it's safer to Alter
than Drop
and Create
. The below will Alter
the Function
if it exists or Create
it if doesn't:
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
...