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.
- In the Object Explorer in SQL Server Management Studio, go to the database and expand it.
- Expand the Programmability folder.
- Expand the Functions folder. Under the function folder, you can find sub folders for each type of UDF.
- Right click one of the folders and select Filter >> Filter Settings.
- In the filter settings window, enter the partial name of the function against the Name property. Click OK.
- In the Function type dub folder, only the UDFs containing the name will be listed.
- Multiple methods to search and find stored procedures in SQL Server.