Tables used by a job

Xanadu · Post by **Xanadu** » Wed Jul 28, 2004 10:13 am

hello..
is there any easy way to figure which of the jobs use a particular database table or the other way round ( which of the tables are being used by a job) other than manually verifying each job configuration ?
(Environment : SQL Server, Windows, DS 7.1)

Please let me know.
Thanks in adv
-Xanadu

chucksmith · Post by **chucksmith** » Wed Jul 28, 2004 1:16 pm

I have a tool on my site, www.anotheritco.com on the tools page. It list all files and tables used by jobs in a dsx file.

You will want to export your job designs (only), copy the export file to your server, and use it as the input file when you run the tool.

Please let me know if this help.

chucksmith · Post by **chucksmith** » Wed Jul 28, 2004 1:19 pm

Since you are on a Windows based server, you may need to change the Line Termination setting in the sequential file stages from Unix to DOS.

ariear · Post by **ariear** » Wed Jul 28, 2004 1:50 pm

You can use MANAGER's 'Usage Analysis' (if you didn't alter the columns in your job after loading the columns from 'TABLE DEFINITION'
Or as suggested use a script for this (there are a couple 'round this forum)

Xanadu · Post by **Xanadu** » Thu Jul 29, 2004 9:29 am

chucksmith wrote:I have a tool on my site, www.anotheritco.com on the tools page. It list all files and tables used by jobs in a dsx file.

You will want to export your job designs (only), copy the export file to your server, and use it as the input file when you run the tool.

Please let me know if this help.

Thank you so much Chuck. Its been very useful. I tried it with only jobs. I should try it with other elements like routines, transforms etc too.

Thanks again. The tool is awesome.
~Xanadu

Xanadu · Post by **Xanadu** » Thu Jul 29, 2004 9:36 am

ariear wrote:You can use MANAGER's 'Usage Analysis' (if you didn't alter the columns in your job after loading the columns from 'TABLE DEFINITION'
Or as suggested use a script for this (there are a couple 'round this forum)

Thanks arier. I used Chuck's tool. The actual issue is that there are a few tables in the source and we want to know which jobs,routines,etc are using them.
Usage analysis takes the job or routine as input but I want my task the other way round. It should take the table name as input and gnerate output. I used Chuck's tool and searched for the table name. But I used it for only jobs. I should run it for routines..

Thanks again
~Xan

chucksmith · Post by **chucksmith** » Thu Jul 29, 2004 9:52 am

Unfortunately, my list files and tables tool will only find them in jobs, not in routines.

However, if you know the file or table names you are searching for, you could use my search tool to locate jobs and routines that contain the string (table/file name).

In most routines, files are accessed via open and read commands. I would create a dsx of just routines, then use the search tool for open and read to identify potential routines for your review.

Thank you for the feedback. :D