Post reply

Sql query to generate script for all jobs in sql server instance

  • February 11, 2013 at 3:05 am

    #269931

    Hi Friends,

    I need a T-SQL, which scripts all the jobs in the sql server instance. Thanks in advance.

  • February 11, 2013 at 5:10 am

    #1586469

    I haven't come across a TSQL query for this.

    I use PowerShell, see below:

    [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | Out-Null

    $ps = New-Object Microsoft.SqlServer.Management.Smo.Server $YourInstance

    $ps.JobServer.Jobs | % {$_.Script()}


    Check Your SQL Servers Quickly and Easily
    www.sqlcopilot.com

  • February 11, 2013 at 8:37 pm

    #1586818

    Side note: The % sign in the script above is a shortcut for the Foreach-Object cmdlet.

    If this absolutely must be in T-SQL then start Profiler on a test instance, right-click a job in SQL Agent and select > Script Job As > Create To > New Query window. SSMS uses SMO just like the PowerShell example Richard showed and in this way you can see exactly what SQL the UI operation generates so you can use that to suit your needs.

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply