Find User Defined Function Containing Text or Table or Column Name in SQL Server

In my earlier article, I’ve explained the methods to find a user defined function or UDF by its full or partial name. Here, in this article, I’ve listed down different methods to find one or more user defined functions by the contents of their definition, like finding the UFDs which are using a specific table name or column name.

You can find the UDFs which are using a specific table or column by using the system catalog views like Information_Schema.Routine, Syscomments and sys.sql_modules. Here are the sample scripts to find user defined function containing Text or object name.

1. Using Information_Schema.Routines

Using the ROUTINE_DEFINITION column of INFORMATION_SCHEMA.ROUTINES, you can find the functions which are using a specific table or column, or containing a specific text.

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

2. Using Syscomments

Just like INFORMATION_SCHEMA.ROUTINES, you can use the SYSCOMMENTS system view to find the user function containing the particular text. Below is the sample SQL script for using syscomments for find the function.

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

3. Using Sys.sql_modules

Here is another way to find the user defined functions containing a text using the sys.sys_module system view.

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

Reference

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