September 24, 2015 at 3:42 pm
I have a new production server with about 100 jobs on it. These are ETL jobs about half of which are in SSIS packages and half call stored procedures directly. For those calling stored procedures directly, might there be a way to use the system catalog to link the schedule of the job to the table being updated by the procedure? The result is a list of tables (those updated by jobs) and the schedule of when they are updated.
John
SQL 2012 Standard VPS Windows 2012 Server Standard
September 25, 2015 at 3:10 am
Not sure what you are trying to do..
if it is like few stored procedures are called via sql agent and you would like to know which job is updating which table inside the stored proc, then it is possible but little complex. I can provide the list of tables that you need to be accessing for the time being.
1) Sysjobs, sysjobschedules for job details
2) sysjobsteps for stored procedure
3) database.dbo.syscomments for stored procedure code..
You may attempt to extract data out of these to get the query you are looking for.
Regards,
Raj
September 25, 2015 at 3:51 pm
I can see it would take a couple of steps to tie an update time to a table this way. TSQL to pull out the table name from a syscomments is the piece I'm not sure I know how to attack. Any ideas?
John
SQL 2012 Standard VPS Windows 2012 Server Standard
September 26, 2015 at 1:47 am
Probably useless as a suggestion, but could you add logging to the SProcs which stored SProc name, Table name, and Date started / finished?
September 26, 2015 at 2:39 am
Johnny B (9/25/2015)
I can see it would take a couple of steps to tie an update time to a table this way. TSQL to pull out the table name from a syscomments is the piece I'm not sure I know how to attack. Any ideas?John
Depends on the stored proc.
For example - Select object_name(syscomments.id),sys.objects.name from syscomments,sys.objects where
syscomments.text like '%Update ' + sys.objects.name + '%'
and sys.objects.type = 'U'
If you use aliases in update statements, it of course becomes harder to trace.
Regards,
Raj
September 28, 2015 at 10:19 am
I like this idea and it has been implemented for some of the SProcs here. Problem is, the code used is cumbersome and makes a 15 line SProc turn into a 50 liner. Consequently, the concept is not well accepted by the organization.
Can I interest you in elaborating on a good implementation of this kind of logging?
SQL 2012 Standard VPS Windows 2012 Server Standard
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply