When managing multiple SQL Server instances, you'll need to extract job information programmatically. The msdb.dbo.sysjobs system table contains all agent job metadata:

SELECT 
    job_id,
    name AS job_name,
    enabled,
    description,
    date_created,
    date_modified
FROM msdb.dbo.sysjobs
ORDER BY name;

To get complete scheduling information, join with the sysjobschedules and sysschedules tables:

SELECT 
    j.name AS job_name,
    j.enabled,
    s.name AS schedule_name,
    CASE s.freq_type
        WHEN 1 THEN 'Once'
        WHEN 4 THEN 'Daily'
        WHEN 8 THEN 'Weekly'
        WHEN 16 THEN 'Monthly'
        WHEN 32 THEN 'Monthly relative'
        WHEN 64 THEN 'When SQL Server Agent starts'
        ELSE 'Unknown'
    END AS frequency_type,
    s.active_start_time,
    s.active_end_time
FROM msdb.dbo.sysjobs j
LEFT JOIN msdb.dbo.sysjobschedules js ON j.job_id = js.job_id
LEFT JOIN msdb.dbo.sysschedules s ON js.schedule_id = s.schedule_id
ORDER BY j.name;

This comprehensive query formats data for direct spreadsheet import:

SELECT 
    j.name AS [Job Name],
    CASE j.enabled WHEN 1 THEN 'Yes' ELSE 'No' END AS [Enabled],
    j.description AS [Description],
    CONVERT(VARCHAR, j.date_created, 120) AS [Created Date],
    CONVERT(VARCHAR, j.date_modified, 120) AS [Modified Date],
    s.name AS [Schedule Name],
    CASE 
        WHEN s.freq_type = 1 THEN 'One Time'
        WHEN s.freq_type = 4 THEN 'Daily'
        WHEN s.freq_type = 8 THEN 'Weekly on ' + 
            CASE WHEN s.freq_interval&1=1 THEN 'Sun, ' ELSE '' END +
            CASE WHEN s.freq_interval&2=2 THEN 'Mon, ' ELSE '' END +
            CASE WHEN s.freq_interval&4=4 THEN 'Tue, ' ELSE '' END +
            CASE WHEN s.freq_interval&8=8 THEN 'Wed, ' ELSE '' END +
            CASE WHEN s.freq_interval&16=16 THEN 'Thu, ' ELSE '' END +
            CASE WHEN s.freq_interval&32=32 THEN 'Fri, ' ELSE '' END +
            CASE WHEN s.freq_interval&64=64 THEN 'Sat, ' ELSE '' END
        WHEN s.freq_type = 16 THEN 'Monthly on day ' + CAST(s.freq_interval AS VARCHAR)
        ELSE 'Custom'
    END AS [Schedule Details],
    STUFF(STUFF(RIGHT('000000' + CAST(s.active_start_time AS VARCHAR),6),3,0,':'),6,0,':') AS [Start Time]
FROM msdb.dbo.sysjobs j
LEFT JOIN msdb.dbo.sysjobschedules js ON j.job_id = js.job_id
LEFT JOIN msdb.dbo.sysschedules s ON js.schedule_id = s.schedule_id
ORDER BY j.name;

For direct export to CSV, use SQLCMD with output redirection:

sqlcmd -S your_server -d msdb -Q "SELECT name, enabled FROM sysjobs" -o "C:\temp\jobs.csv" -h-1 -s","

Create a reusable PowerShell script for regular exports:

$server = "your_server"
$outputFile = "C:\reports\sql_jobs_$(Get-Date -Format 'yyyyMMdd').csv"

$query = @"
SELECT 
    j.name AS JobName,
    j.enabled AS IsEnabled,
    j.description AS Description
FROM msdb.dbo.sysjobs j
"@

Invoke-Sqlcmd -ServerInstance $server -Database "msdb" -Query $query |
    Export-Csv -Path $outputFile -NoTypeInformation

When managing SQL Server environments, administrators often need to export job details for documentation or analysis. Manually collecting this information is inefficient, especially in environments with numerous jobs. This article demonstrates several T-SQL approaches to programmatically extract SQL Server Agent job metadata.

The most straightforward method queries the msdb.dbo.sysjobs system table:

SELECT 
    job_id,
    name AS job_name,
    enabled,
    description,
    date_created,
    date_modified
FROM 
    msdb.dbo.sysjobs
ORDER BY 
    name;

For a more detailed report including schedule information:

SELECT 
    j.job_id,
    j.name AS job_name,
    j.enabled,
    j.description,
    js.next_run_date,
    js.next_run_time,
    s.name AS schedule_name,
    s.enabled AS schedule_enabled,
    s.freq_type,
    s.freq_interval,
    s.freq_subday_type,
    s.freq_subday_interval,
    s.freq_relative_interval,
    s.freq_recurrence_factor,
    s.active_start_date,
    s.active_end_date,
    s.active_start_time,
    s.active_end_time
FROM 
    msdb.dbo.sysjobs j
LEFT JOIN 
    msdb.dbo.sysjobschedules js ON j.job_id = js.job_id
LEFT JOIN 
    msdb.dbo.sysschedules s ON js.schedule_id = s.schedule_id
ORDER BY 
    j.name;

To include step-level details in your export:

SELECT 
    j.name AS job_name,
    js.step_id,
    js.step_name,
    js.subsystem,
    js.command,
    js.database_name,
    js.on_success_action,
    js.on_fail_action,
    js.retry_attempts,
    js.retry_interval
FROM 
    msdb.dbo.sysjobs j
INNER JOIN 
    msdb.dbo.sysjobsteps js ON j.job_id = js.job_id
ORDER BY 
    j.name, js.step_id;

To directly export results to a file for spreadsheet import:

-- Method 1: Using Results to File in SSMS
-- 1. Execute query
-- 2. Right-click results → Save Results As...
-- 3. Choose CSV format

-- Method 2: Using bcp utility
EXEC xp_cmdshell 'bcp "SELECT * FROM msdb.dbo.sysjobs" queryout "C:\temp\jobs.csv" -c -t, -T -S YourServerName'

For documentation or migration purposes, you might need the actual T-SQL scripts:

SELECT 
    j.name,
    CONVERT(NVARCHAR(MAX), 
    'EXEC msdb.dbo.sp_add_job @job_name=N''' + j.name + ''', @enabled=' + 
    CONVERT(NVARCHAR(10), j.enabled) + ', @description=N''' + 
    ISNULL(j.description, '') + ''';' + CHAR(13) + CHAR(10) +
    -- Additional script generation logic would go here
    '-- Add steps and schedules as needed'
    ) AS create_script
FROM 
    msdb.dbo.sysjobs j;

Common filtering scenarios:

-- Get only enabled jobs
SELECT * FROM msdb.dbo.sysjobs WHERE enabled = 1;

-- Find jobs containing specific text
SELECT * FROM msdb.dbo.sysjobs WHERE name LIKE '%backup%';

-- Jobs modified in last 30 days
SELECT * FROM msdb.dbo.sysjobs 
WHERE DATEDIFF(DAY, date_modified, GETDATE()) <= 30;

-- Jobs with specific owner
SELECT * FROM msdb.dbo.sysjobs 
WHERE owner_sid = SUSER_SID('YourDomain\ServiceAccount');

To view SQL Agent jobs, users need:

-- Minimum required permission
GRANT SELECT ON msdb.dbo.sysjobs TO [YourUser];

-- Recommended role membership
EXEC msdb.dbo.sp_addrolemember @rolename = 'SQLAgentReaderRole', @membername = 'YourUser';