Archive note: originally published October 25, 2009, and kept for the record. The msdb job
tables it queries are unchanged, so the query still runs as written.
I have a habit of not only categorizing my jobs meticulously, but also prefixing those that are of DBA use/interest only with “DBA: “. It keeps them visually grouped, for both my and others’ benefit. It also lets me run quick little queries like this.
-- summarize dba jobs
select
[Server] = Convert(varchar, ServerProperty('ServerName')),
[Category] = c.[name],
[Job Name] = j.[name],
[Enabled] = j.[enabled],
[Step #] = s.[step_id],
[Step Name] = s.[step_name],
[Subsystem] = s.[subsystem],
[Command] = s.[command]
from
msdb.dbo.sysjobs [j]
inner join msdb.dbo.syscategories [c] on j.[category_id] = c.[category_id]
inner join msdb.dbo.sysjobsteps [s] on j.[job_id] = s.[job_id]
where
j.[name] like 'DBA: %'
order by
c.[name] asc,
j.[name] asc,
s.[step_id] asc ;Running that gives me a quick overview of what maintenance scripts are setup on a particular server - assuming the prefix is used…
The habit outlasted the job. Naming things so they can be inventoried later is the same instinct that shows up in PowerClip and Find-InFiles a decade after this, and it’s the last item in Eleven SQL Server Checks, Ordered by Return on the Hour Spent.