Searching SQL Server Views, Stored Procedures, and Functions for a Specific String

Whether you’re cleaning up legacy code, troubleshooting a data issue, or just trying to track down where a particular table, field, or string is used, being able to search through your database objects quickly can save a ton of time.

While this tip isn’t exclusive to Infor CRM, it’s something every Infor CRM developer will run into eventually — especially when dealing with customizations, integrations, or simply trying to understand what’s going on behind the scenes.

Here’s a handy SQL script that will search through view definitions, stored procedures, and functions to find any occurrence of a specific string.

Use Case

Let’s say you want to find where CustomerID is referenced in your database. It could be in a view used for reporting, in a stored procedure that runs during a job, or in a function used for business logic. Instead of checking each one manually, just run the following script:

SELECT 
    o.name AS ObjectName,
    s.name AS SchemaName,
    o.type_desc AS ObjectType,
    m.definition AS ObjectDefinition
FROM 
    sys.sql_modules m
JOIN 
    sys.objects o ON m.object_id = o.object_id
JOIN 
    sys.schemas s ON o.schema_id = s.schema_id
WHERE 
    m.definition COLLATE Latin1_General_CI_AS LIKE '%CustomerID%'
    AND o.type IN ('V', 'P', 'FN', 'IF', 'TF');  -- Views, Procs, Scalar, Inline &amp; Table-valued Functions

Object Types Explained

The script filters to include the most common programmable objects:

'V' = View
'P' = Stored Procedure
'FN' = Scalar Function
'IF' = Inline Table-valued Function
'TF' = Table-valued Function

You can add or remove types depending on what you’re interested in.

Why This Matters for Infor CRM

Infor CRM relies on many database objects for customizations — views used in reports or filters, procedures called by web or job services, and functions that compute logic behind the scenes. If you’re working with a heavily customized environment, knowing exactly where a field is referenced helps:

Track down business logic affecting a specific entity
Understand data dependencies when planning schema changes
Safely rename or deprecate old fields or tables
Identify performance or data flow issues

If you found this helpful, stay tuned for more SQL Server tips — especially those useful in the context of Infor CRM development or troubleshooting. Got a cool query of your own? Let me know in the comments!

About the Author

2 COMMENTS

Hi,

I made this one to search in code source some keyword :

POSGRESQL

-- Usage select * from fn_search_source_code(ma_recherche)
CREATE OR REPLACE FUNCTION fn_search_source_code(search_text text)
RETURNS TABLE(package_name varchar, schema_name varchar, manager_name varchar, search_result text) AS $$
BEGIN
   RETURN QUERY
	select 
		pack."Name"::varchar as package_name,
		sch."Name"::varchar as schema_name, 
		sch."ManagerName"::varchar as manager_name,
		substring(convert_from(content."Content", 'UTF-8'), position(search_text in convert_from(content."Content", 'UTF-8')), 200)::text as search_result
	from "SysSchemaContent" content
	join "SysSchema" sch on sch."Id" = content."SysSchemaId"
	join "SysPackage" pack on pack."Id" = sch."SysPackageId"
	where convert_from(content."Content", 'UTF-8') like concat('%', search_text, '%')
	;
END
$$ LANGUAGE plpgsql;

MSSQL

/*
	Usage: select * from udf_search_source_code('groupAnd.addItem');
*/
ALTER   FUNCTION [dbo].[udf_search_source_code] (
	@search_text text
)
RETURNS TABLE
AS
RETURN
	select 
	pack.Name as package_name,
	sch.Name as schema_name,
	sch.ManagerName as manager_name,
	substring(
		Cast(content.Content as varchar(max)), 
		patindex(
			concat('%',@search_text ,'%'), 
			Cast(content."Content" as varchar(max))
		), 
		200
	) as search_result
	from SysSchemaContent content
	join "SysSchema" sch on sch."Id" = content."SysSchemaId"
	join "SysPackage" pack on pack."Id" = sch."SysPackageId"
	where Cast(content.Content as varchar(max)) like concat('%', @search_text, '%');

Reply

Ryan Farley April 22, 2025 At 10:21 am

Thanks for sharing that Jérôme. That will definitely come in handy for tracking down things in Creatio C# code!
Ryan

Searching SQL Server Views, Stored Procedures, and Functions for a Specific String