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.
[crayon-5d5a8bf3058fd813552578 inline="true" ]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.