CODEWITHSUNDEEP
sqlsql-server
KumarV
KumarV

Reputation: 11

T-SQL query to find jobs and tables

I want to find the sql server agent jobs that operate upon a particular table. For ex: I have a table called TAB1 which is updated daily by a job called SAJ1. I need a query to extract this information.

Upvotes: 1

Views: 5599

Answers (2)

KumarV
KumarV

Reputation: 11

One of the options available is the table msdb.dbo.sysjobsteps that enlists all the jobs and their steps. You can go into the Command column of the output and look for required information.

select * from msdb.dbo.sysjobsteps;

Upvotes: 0

János Spengler
János Spengler

Reputation: 109

You can start with this select if the job step in T-SQL then it will find the text what you will declare as table name.

use msdb
Declare @table_name varchar(50)

set  @table_name='Test'

select j.name,js.command from dbo.sysjobs j
inner join
dbo.sysjobsteps js
on j.job_id=js.job_id
where command like '%'+@table_name+'%'

Upvotes: 1

Related Questions