I started a new full-time contract this month, which is always weird. Not having a full understanding of the project, access rights roadblocks, and unfamiliarity with the code base all team up to make days feel long and unproductive.
One of the things I needed to do is to find where data is regularly getting imported into a database. I came up with this query on msdb to show me scheduled tasks that run FTP commands or DTS packages and had been run during this calendar year.
SELECT j.name AS job_name
, j.description AS job_description
, st.step_name AS step_name
FROM msdb.dbo.sysjobsteps st
JOIN msdb.dbo.sysjobs j on st.job_id = j.job_id
WHERE 1 = 1
AND (st.command LIKE '%ftp%' or st.command LIKE 'dtsrun%' )
AND st.last_run_date LIKE '2014%'
AND j.enabled = 1
The items in the where clause are pretty much self-explainitory and can be changed to meet the need of any SQL detective. It’s one I’ll keep in my toolbox for a while, I suspect.
Aside: what’s with the 1 = 1 at the top of the WHERE clause?
If I’m doing a lot of commenting and uncommenting of conditions, I like to just start my WHEREs with 1 = 1. That way, I don’t have to worry about whether or not I need to include the AND when I comment something. I can just start dropping “–” at the start of each line and not think about it any more than that.