Search And Find User Defined Function (UDF) By Name In SQL Server

I often used to search and find user defined function in SQL Server database with lots of UDFs. I may not always remember the full name of the UDF. Instead, I’ll be in a situation to find the function with it’s partial names. In a huge application, there may be hundreds of stored procedures, views and user defined functions. Looking for a specific UDF is difficult and time-consuming to identify it from the list of all the UDFs. But there are several techniques to search and find user defined function by its name. Here I’ve provided few methods to search and find the user defined functions or UDF by its full or partial name.

Find User Defined Function (UDF) by Its Name or Partial Name

Using Transact-SQL Statements

To find a user defined function by its full name or part of the name, you can use transact SQL statements against the system tables like sys.objects, sys.procedures, Information_Schema.Routines or syscomments.

1. Using Sys.objects

Using the sys.objects system view you can get list of UDFs by filtering the object type. In order to search for a specific UDF by its name, use the name column of sys.object.

USE WideWorldImporters;
GO
SELECT
     Name AS 'Function Name',
     SCHEMA_NAME(schema_id) AS 'Schema',
     type_desc AS 'Function Type', 
     create_date AS 'Created Date'
FROM
     sys.objects
WHERE
     Type in ('FN', 'IF', 'FN', 'AF', 'FS', 'FT') And
     Name like '%Price%';
GO

2. Using Information_Schema.Routines

Using Information_Schema.Routines, you can filter the UDFs with the Routine_Name column. Here is the sample query.

USE WideWorldImporters;
GO
SELECT
    ROUTINE_NAME, 
    ROUTINE_DEFINITION , 
    ROUTINE_SCHEMA, 
    DATA_TYPE,
    CREATED
FROM
    INFORMATION_SCHEMA.ROUTINES 
WHERE
    ROUTINE_TYPE = 'FUNCTION' AND
    ROUTINE_NAME LIKE '%Price%';
GO

3. Using Syscomments

In Syscomments, you can filter the UDFs using the OBJECT_NAME and OBJECTPROPERTY functions. Below the sample code.

USE WideWorldImporters;
GO
SELECT
    DISTINCT OBJECT_NAME(id)
FROM
    syscomments
WHERE
    (OBJECTPROPERTY(id, 'IsInlineFunction') = 1 OR
    OBJECTPROPERTY(id, 'IsScalarFunction') = 1 OR
    OBJECTPROPERTY(id, 'IsTableFunction') = 1) AND
    OBJECT_NAME(id) LIKE '%Price%'
GO

4. Using Sys.sql_modules

Just like Syscomments you can use Sys.sql_modules system catalog view as well. Here is the sample query.

USE WideWorldImporters;
GO
SELECT
    DISTINCT OBJECT_NAME(object_id) AS 'Function Name', 
    OBJECT_SCHEMA_NAME(object_id) AS 'Schema Name'
FROM
    sys.sql_modules
WHERE
    (OBJECTPROPERTY(object_id, 'IsInlineFunction') = 1 OR
    OBJECTPROPERTY(object_id, 'IsScalarFunction') = 1 OR
    OBJECTPROPERTY(object_id, 'IsTableFunction') = 1) AND
    OBJECT_NAME(object_id) LIKE '%Price%';
GO

Using Filter Settings In SSMS Object Explorer GUI

Yet another easiest way to find the user defined function by its partial name is using the SQL Server Management Studio (SSMS) Object Explorer window’s filter settings. In case if you are not familiar with this method, just follow the below steps.

1. In the Object Explorer in SQL Server Management Studio, go to the database and expand it.
2. Expand the Programmability folder.
3. Expand the Functions folder. Under the function folder, you can find sub folders for each type of UDF.
4. Right click one of the folders and select Filter >> Filter Settings.
   
5. In the filter settings window, enter the partial name of the function against the Name property. Click OK.
   
6. In the Function type dub folder, only the UDFs containing the name will be listed.

You may be interested in finding UDFs having text or table or column name.

Reference

• Multiple methods to search and find stored procedures in SQL Server.