A common task within SQL is to search an objects definition for a particular reference. This might be a table, column, procedure, function or view name. Below are a few techniques for achieving this. I’ve also highlighted some tables that are related to SQL Server Maintenance Plans and how to start searching interrogating the data tied to these.

/* 

Searching Object Definitions in SQL.

sys.sql_modules is a fairly complete way to achieve this but there's a myriad of options below, including a way 
to look into SQL Maintainence plans (add a where clause on the command column to filter this down)

*/

DECLARE
@SEARCH_TERM		NVARCHAR(MAX) = '%tblSYS_SCHEMA_COLUMN%';

--Full raw objects that reference tempID (or the SEARCH_TERM) in Views (for reference, select only), Triggers, SP's and Functions
SELECT
so.name
, sm.[definition]
, so.type_desc
FROM sys.sql_modules sm
	INNER JOIN sys.objects so ON sm.object_id = so.object_id
WHERE sm.[definition] LIKE @SEARCH_TERM
ORDER BY so.type_desc, so.name;

--Raw results from sys.procedures (SP's only) - Use for verification with the above sys.sql_modules results
SELECT *
FROM sys.procedures sp
WHERE OBJECT_DEFINITION(OBJECT_ID) LIKE @SEARCH_TERM
ORDER BY sp.name;

--Results from the INFORMATION_SCHEMA.ROUTINES system view (encapsulating SP's and Functions) - Use for verification with the above sys.sql_modules results
SELECT
ir.SPECIFIC_NAME
, ir.ROUTINE_TYPE
, ir.*
FROM INFORMATION_SCHEMA.ROUTINES ir
WHERE ir.ROUTINE_DEFINITION LIKE @SEARCH_TERM
ORDER BY ir.ROUTINE_TYPE DESC, ir.SPECIFIC_NAME;

--Check SQL Jobs (Maintenance Plans) for the search term being imbedded in the 
SELECT
sj.name
, sj.[enabled]
, sjs.step_name
, sjs.subsystem
, sjs.command		--Filter the command (by adding a WHERE clause) column by the search term (TSQL subsystems)
FROM msdb.dbo.sysjobs sj
	INNER JOIN msdb.dbo.sysjobsteps sjs ON sj.job_id = sjs.job_id
WHERE sjs.command LIKE @SEARCH_TERM
ORDER BY sj.name;

/*
SQL JOB TABLES FOR REFERENCE

SELECT * FROM msdb.dbo.SysJobs
SELECT * FROM msdb.dbo.SysJobSteps
SELECT * FROM msdb.dbo.SysJobSchedules
SELECT * FROM msdb.dbo.SysJobServers
SELECT * FROM msdb.dbo.SysJobHistory
*/