Searching for Jobs that use a particular table

[sirishds]

HI Gurus,

I need to list all the jobs in a project for which a particular table is being used.

The table name supposed to find is "R_DM_SCRB_DTL' ". In the jobs as per the convention, we need to prefix 'ora' or 'src' something as such before the table name so in the below query I used DS_JOBOBJECTS.NAME like '%_R_DM_SCRB_DTL'

I used the below query:
SELECT distinct DS_JOBS.NAME AS JOB_NAME FROM DS_JOBS,DS_JOBOBJECTS WHERE DS_JOBS.JOBNO = DS_JOBOBJECTS.OBJIDNO AND DS_JOBOBJECTS.NAME like '%_R_DM_SCRB_DTL' AND CATEGORY LIKE '\Global\RDM\Supply Chain%';

Query works and it helps me to find if any such table is used in the job.

But in my current scenario I have a job (job_name: j_P_ITEM).P_item(table_name) is used as a source and named as ora_P_ITEM (oracle_connector stage) used. Internally in the query left outer join is performed on R_DM_SCRB_DTL'. I would also need to list such things. If 'R_DM_SCRB_DTL' is used internally in the query even need to list such jobs. Is there a way that I can write a query in command line to pull the list of such jobs? Kindly help

Thanks in advance.
Kumar

[ray.wurlod]

You need an additional filter to check the entire DS_JOBOBJECTS record for the (possibly partial) name of the table, maybe constraining that to records for which OLETYPE ends in "put". Use EVAL and @RECORD in the query - search DSXchange for examples.

[asorrell]

If you have access to Metadata Workbench and are familiar with it, you might also consider using that.