Objects used by SQL Server job

Question

I have a nightly SQL Server job, scheduled on SQL Agent.

It uses a number of databases, stored procedures and tables.

What is the best to get a list of these?

(Its SQL Server 2000)

Answer 1

You can get the command text of each step of your job with the following query:

SELECT  sj.name         AS job_name
        ,sjs.step_id
        ,sjs.step_name
        ,sjs.command 
FROM msdb.dbo.sysjobs           AS sj
JOIN msdb.dbo.sysjobsteps       AS sjs
ON   sj.job_id = sjs.job_id
WHERE sj.name = '~your job name~'
AND   sjs.subsystem = 'TSQL'
ORDER BY sj.name
        ,sjs.step_id

You can then search through the command text of each job step to look for database, table and stored procedure names. This is simpler if you have consistent naming conventions for your objects (e.g. table names start t_, stored procedure names start usp_). Remember to exclude comments in your search.

A regular expression library makes this task easier, but support for them is not included in native T-SQL. There is more than one way to add regular expression support to SQL 2000, or you could use another language/tool to carry out the searches.

Unfortunately, depending on the complexity of the T-SQL commands, there may be no 100% guaranteed way of identifying object names without writing (or buying in) a T-SQL parser.