Many a times we come across a scenario where we need to execute some code based on whether a User Defined Function exists or not. There are different ways of identifying the Function existence in Sql Server, in this article will list out the commonly used approaches. Let me know which approach you use and reason for the same.

To demonstrate these different approaches let us create a sample database with an Inline Table Valued function by executing the following script:

CREATE DATABASE SqlHintsFunctionExists
GO
USE SqlHintsFunctionExists
GO
--Create Inline Table Valued function
CREATE FUNCTION dbo.GetEmployeeDetail()
RETURNS TABLE
AS
RETURN (SELECT 1 AS Id, 'Basavaraj' AS Name)
GO

[ALSO READ] How to check if a Stored Procedure exists in Sql Server

Approach 1: Using sys.objects catalog view

We can write a query like below to check if a GetEmployeeDetail User Defined Function exists in the current database in any schema.

USE SqlHintsFunctionExists
GO
IF EXISTS (SELECT 1 FROM sys.objects 
           WHERE Name = 'GetEmployeeDetail' 
             AND Type IN ( N'FN', N'IF', N'TF', N'FS', N'FT' ))
BEGIN
    PRINT 'User defined function Exists'
END

RESULT:

The above query checks the existence of the object whose name is GetEmployeeDetail across all the schemas in the current database which is one of the below function type:

Type	Type Description
FN	Scalar function
IF	Inline table-valued function
TF	Table-valued-function
FS	Assembly (CLR) scalar-function
FT	Assembly (CLR) table-valued function

In Sql Server object names are unique, so it is really not required to mention the type in the where clause of the sys.objects query. But it is always better practice to mention type, reason is sys.objects contains the rows for not just function it also has records for views, stored Procedures etc
If you want to check the existence of the User Defined Function in a specified Schema, then we can re-write the query like below:

USE SqlHintsFunctionExists
GO
IF EXISTS (SELECT 1 FROM sys.objects 
           WHERE Name = 'GetEmployeeDetail' 
	    AND schema_id = SCHEMA_ID('dbo') --Schema name 'dbo'
	    AND Type IN ( N'FN', N'IF', N'TF', N'FS', N'FT' ))
BEGIN
    PRINT 'User defined function Exists'
END

RESULT:

If you want check the existence of a user defined function in a database other than the contextual database then we can re-write the above query as below where sys.objects is specified by three part name:

IF EXISTS (SELECT 1 FROM SqlHintsFunctionExists.sys.objects 
           WHERE Name = 'GetEmployeeDetail' 
            AND schema_id = SCHEMA_ID('dbo') --Schema name 'dbo'
            AND Type IN ( N'FN', N'IF', N'TF', N'FS', N'FT' ))
BEGIN
    PRINT 'User defined function Exists'
END

RESULT:

[ALSO READ] How to check if a Database exists in Sql Server

Approach 2: Using OBJECT_ID() function

We can use OBJECT_ID() function like below to check if a GetEmployeeDetail function exists in the current database.

USE SqlHintsFunctionExists
GO
IF OBJECT_ID(N'dbo.GetEmployeeDetail') IS NOT NULL
BEGIN
    PRINT 'User defined function Exists'
END

RESULT:

Specifying the Database Name and Schema Name parts for the Function Name is optional. But specifying Database Name and Schema Name provides an option to check the existence of the user defined function in the specified database and within a specified schema, instead of checking in the current database across all the schemas. The below query shows that, even though the current database is MASTER database, we can check the existence of the GetEmployeeDetail function in the dbo schema in the SqlHintsFunctionExists database.

USE MASTER
GO
IF OBJECT_ID(N'SqlHintsFunctionExists.dbo.GetEmployeeDetail')
           IS NOT NULL
BEGIN
    PRINT 'User defined function Exists'
END

RESULT:

OBJECT_ID() function also accept the type of the object as a parameter. We can specify one of the type of the function listed in the approach 1. In the below example we are checking whether GetEmployeeDetail Table-Valued function (i.e type IF) existing in the SqlHintsFunctionExists database within the dbo schema.

USE MASTER
GO
IF OBJECT_ID(N'SqlHintsFunctionExists.dbo.GetEmployeeDetail'
             , N'IF') IS NOT NULL
BEGIN
    PRINT 'User defined function Exists'
END

RESULT:

Let us modify the above statement and specify the type as ‘FN’ (i.e. Scalar function):

USE MASTER
GO
IF OBJECT_ID(N'SqlHintsFunctionExists.dbo.GetEmployeeDetail'
             , N'FN') IS NOT NULL
	BEGIN
		PRINT 'User defined function Exists'
	END
ELSE
	BEGIN
		PRINT 'User defined function doesn''t Exists'
	END

RESULT:

From the result we can see that the function GetEmployeeDetail doesn’t exists in the SqlHintsFunctionExists database. This is because we have specified the type of the object as FN (i.e. scalar function) in OBJECT_ID function instead of IF (i.e. Table-Valued function).

[ALSO READ] How to check if a Table exists in Sql Server

Approach 3: Using INFORMATION_SCHEMA.ROUTINES View

We can use the INFORMATION_SCHEMA.ROUTINES view to check the existence of the function as shown below:

USE SqlHintsFunctionExists
GO
IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.ROUTINES 
           WHERE ROUTINE_NAME = 'GetEmployeeDetail' 
            AND ROUTINE_TYPE = 'FUNCTION') 
	BEGIN
		PRINT 'User defined function Exists'
	END
GO

RESULT:

This view does has the ROUTINE_SCHEMA column, but as suggested in MSDN we should avoid using it for identifying the schema of the object.

[ALSO READ] How to check if Temp table exists in Sql Server?

Approach 4: Avoid Using sys.sysobjects System table

We should avoid using sys.sysobjects System Table directly, direct access to it will be deprecated in some future versions of the Sql Server. As per Microsoft BOL link, Microsoft is suggesting to use the catalog view sys.objects instead of sys.sysobjects system table directly to check the existence of the function.

USE SqlHintsFunctionExists
GO
IF EXISTS(SELECT * FROM sys.sysobjects  
     WHERE name = 'GetEmployeeDetail' 
      AND Type IN ( N'FN', N'IF', N'TF', N'FS', N'FT' ))
BEGIN
	PRINT 'User defined function Exists'
END

RESULT: