Find-sqmAgentJobReference

Inventory & SearchsqmSQLTool v1.9.131+ · Find🔓 msdb read access

Answers the question that comes up before every cleanup: is there an Agent job that runs this? A stored procedure is about to be dropped or renamed, a database is about to be decommissioned, or a table keeps changing at night and no application admits to it. Until now the answer meant opening SSMS and clicking through the jobs one by one.

All job steps are read once from msdb.dbo.sysjobsteps and matched against an object name (-ObjectName, wildcards allowed, optionally schema- or database-qualified), free text (-SearchText, literal or a regular expression) and/or a database (-Database). Used on its own, -Database lists every job step still working against a database - the check before switching it off. The result is one row per matching job step, with job, step, subsystem, schedule, last run and the matched line including its line number.

Matching deliberately does not use a server-side command LIKE '%name%'. LIKE treats _ as a pattern character, and procedure names with underscores are the normal case: a search for usp_LoadSales would also return uspXLoadYSales. LIKE has no word boundary either, so usp_LoadSalesArchive would come along too. The comparison therefore runs in PowerShell over identifier boundaries that also cover the bracket notation [dbo].[usp_LoadSales].

A text hit alone is no proof of a call, so every hit is rated instead of merely reported: CallType is Execute when the name stands behind EXEC/EXECUTE (optionally qualified, optionally with a return variable), Reference when it only appears somewhere else - a table in a SELECT, a fragment of dynamic SQL, a name in a log grep - and Text for hits produced by -SearchText. InComment flags occurrences inside -- or /* */. Nothing is silently dropped on that basis; the rating is reported and the decision stays with you.

Parameters

Parameter	Type	Required	Default	Notes
-SqlInstance	string[]	Optional	$env:COMPUTERNAME	One or more instances. Each one is queried separately and tagged in the result.
-SqlCredential	PSCredential	Optional	—	SQL or Windows credential. Without it: Windows authentication.
-ObjectName	string	Optional	—	Name of the stored procedure (or any other object). Wildcards `` and `?` are allowed inside the name, and the name may be qualified: `usp_Load`, `dbo.usp_Load`, `Sales.dbo.usp_Load`. A schema part restricts the hit to that schema; a database part sets the database filter unless `-Database` is given explicitly.
-SearchText	string	Optional	—	Text that has to appear in the step command, e.g. `TRUNCATE TABLE` or `sp_send_dbmail`. Compared case-insensitively as a literal substring. Combined with `-ObjectName`, both conditions must match.
-RegexSearch	switch	Switch	$false	Treat `-SearchText` as a .NET regular expression instead of a literal substring.
-Database	string	Optional	—	Database the step has to work against (wildcards allowed). Can be used on its own to list every job step touching a database.
-JobName	string	Optional	*	Restrict the search to jobs matching this name or wildcard.
-Subsystem	string[]	Optional	TSQL, CmdExec, PowerShell	Step types to search - the three that can run a procedure or a query. `All` searches every subsystem including SSIS.
-ExcludeDisabledJobs	switch	Switch	$false	Skip disabled jobs. Off by default: a disabled job still references the object and is usually re-enabled at some point.
-IncludeCommand	switch	Switch	$false	Add the complete step command as `Command`. Without it only the first 300 characters are returned as `CommandPreview`.
-VerifyObject	switch	Switch	$false	Check in `sys.objects` whether the object found actually exists in the resolved database, and report its type. Turns "the job mentions this name" into "the job calls a procedure that is already gone". Cached per database and name.
-EnableException	switch	Switch	$false	Throw exceptions immediately instead of logging and continuing with the next instance.

Execution Flow

Examples

Is there a job that runs this procedure?

Find-sqmAgentJobReference -SqlInstance "SQL01" -ObjectName "usp_LoadSales"

Only the real calls, and does the called procedure still exist?

Find-sqmAgentJobReference -SqlInstance "SQL01" -ObjectName "usp_Load*" -VerifyObject |
    Where-Object CallType -eq 'Execute' |
    Format-Table JobName, StepName, ResolvedDatabase, ObjectExists

Before decommissioning a database: what still works against it?

Find-sqmAgentJobReference -SqlInstance "SQL01","SQL02" -Database "Sales"

Which job empties tables at night - with the full command for review

Find-sqmAgentJobReference -SqlInstance "SQL01" -SearchText "TRUNCATE TABLE" -IncludeCommand

Jobs still calling something that no longer exists

Find-sqmAgentJobReference -SqlInstance "SQL01" -ObjectName "usp_*" -VerifyObject |
    Where-Object { $_.CallType -eq 'Execute' -and $_.ObjectExists -eq $false }

Estate-wide: who sends mail from an Agent job?

$srv = 'SQL01','SQL02','SQL03'
Find-sqmAgentJobReference -SqlInstance $srv -SearchText "sp_send_dbmail" |
    Select-Object SqlInstance, JobName, StepName, IsScheduled, MatchLine, LineText

Notes

Only job steps stored on the instance are searched. A procedure called indirectly - from another procedure, from an SSIS package, from a CLR assembly or through dynamic SQL assembled at runtime - cannot be seen in the step command. Find-sqmDatabaseObject -SearchDefinition covers the call chain inside the databases.

JobLastRunOutcome and StepLastRunOutcome return NeverRun when there is no last run: msdb stores a job step that has never run with last_run_outcome = 0 and last_run_date = 0, and 0 is also the code for Failed - taking the outcome at face value reports every freshly created job as failed.

Comment detection does not parse string literals, so a -- inside a string is treated as a comment. That is why InComment is reported rather than used to drop a row.

← Back

Command Index

Get-sqmAgentJobScheduleReport

Find-sqmDatabaseObject