April 6, 2011 at 12:40 pm
Hello Everyone,
What tables correlate the job to the actual database included within the job. Is there such a table or view? I have checked quite a few tables and I cannot see a link between the jobs and the specific database name or object id that it works on.
Any ideas?
Thank you,
Dave
April 6, 2011 at 12:43 pm
SELECT database_name, * FROM msdb.dbo.sysjobsteps
April 6, 2011 at 12:48 pm
Hi,
Some of the Tables and Views you need to check are
Select * from msdb..sysjobhistory
Select * from msdb..sysjobs
Select * from msdb..sysjobservers
Select * from msdb..sysjobs_view
Select * from msdb..sysjobactivity
Select * from msdb..sysjobsteps
Select * from msdb..sysjobstepslogs
Select * from msdb..sysjobschedules
Select * from msdb..sysdbmaintplan_jobs
Thanks
Parthi
April 6, 2011 at 12:59 pm
Thank you for the answers. Unfortunately I have looked there already. The database name doesn't appear all the time in the sysjobsteps. Especially if you have more than one database in the job step. The table or view I am looking for is probably one of those obscure tables that you scan that has no appearance that it would contain the information you want, but yet it is the golden key.
Tables I have looked at so far:
SELECT * FROM msdb.dbo.sysmaintplan_plans
SELECT * FROM msdb.dbo.sysobjects
SELECT * FROM msdb.dbo.sysdbmaintplans
SELECT * FROM msdb.dbo.sysdbmaintplan_databases
SELECT * FROM msdb.dbo.sysjobs
SELECT * FROM msdb.dbo.sysjobsteps
SELECT * FROM msdb.dbo.sysjobschedules
SELECT * FROM msdb.dbo.sysschedules
SELECT * FROM msdb.dbo.sysjobservers
SELECT * FROM msdb.dbo.sysjobactivity
SELECT * FROM msdb.dbo.sysjobactivity
SELECT * FROM msdb.dbo.sysjobhistory
SELECT * FROM msdb.dbo.sysjobstepslogs
SELECT * FROM msdb.dbo.sysdtspackages90
SELECT * FROM master.dbo.sysobjects
SELECT * FROM master.dbo.sysdatabases
SELECT * FROM msdb.dbo.sysproxies
SELECT * FROM msdb.dbo.systaskids
SELECT * FROM msdb.dbo.syssubsystems
SELECT * FROM msdb.sys.databases
SELECT * FROM msdb.sys.objects
SELECT * FROM msdb.sys.plan_guides
SELECT * FROM msdb.sys.system_objects
So how does SQL store this relation or is it just done in the command string and never formally stored with a relation?
April 6, 2011 at 1:02 pm
Well if you are looking for a three part name in the command text, I guess this will be only in the command column in jobsteps
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply