Generate scripts for trigger in SQL Server

Question

I am using the below code get completed code of triggers in the selected database.

select [definition] +' ' + 'GO' 
from sys.sql_modules m
inner join sys.objects obj on obj.object_id=m.object_id
where obj.type ='TR'

Requirement: I would like to export the above trigger code as C:\TMP\Trigger.sql with help of T-SQL coding instead of Generate scripts using script wizard. I am not so strong in SQL Server. Can someone help me with this?

you could use the results to file option in ssms and mostly you will be fine, dont have an server to test currently,but i dont see an issue with your script — TheGameiswar
– TheGameiswar, Commented Apr 23, 2017 at 11:50
my requirement is I need to export results as .sql which can be integrated as job in sql which will run on different instance so I can go depend on GUI — Franklin
– Franklin, Commented Apr 23, 2017 at 11:59

Dan Guzman · Accepted Answer · 2017-04-23 12:28:30Z

1

To schedule the scripting via a batch job, consider using a Powershell script or SSIS. Powershell script example:

try {

    Add-Type -Path "C:\Program Files\Microsoft SQL Server\130\SDK\Assemblies\Microsoft.SqlServer.Smo.dll";

    $databaseName = "AdventureWorks2014";
    $sqlServerName = ".";
    $scriptPath = "c:\Scripts\TriggerScripts.sql";

    $SMOserver = New-Object Microsoft.SqlServer.Management.Smo.Server($sqlServerName);
    $scriptr = new-object ('Microsoft.SqlServer.Management.Smo.Scripter') ($SMOserver);

    If (Test-Path $scriptPath) {
        Remove-Item $scriptPath;
    }

    $database = $SMOserver.databases[$databaseName]

    $objectsToScript = @();

    foreach($table in $database.Tables) {
        $objectsToScript += $table.Triggers;
    }

    $scriptr.Options.FileName = $scriptPath;
    $scriptr.Options.AnsiFile = $false;
    $scriptr.Options.AllowSystemObjects = $false;
    $scriptr.Options.IncludeIfNotExists = $false;
    $scriptr.Options.NoCommandTerminator = $false;
    $scriptr.Options.ScriptBatchTerminator = $true;

    $null = $scriptr.Script($objectsToScript);

}
catch [Exception] {
    $e = $_.Exception
    $e.ToString();
    throw;
}

answered Apr 23, 2017 at 12:28

Dan Guzman

46.8k3 gold badges52 silver badges77 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Franklin Over a year ago

Thanks Dan . Is there any way to simplify and use T-SQL code or PROCEEDURE instead of PS hence PS is restricted on our prod envt.

Dan Guzman Over a year ago

The tricky part of a T-SQL only solution is creating the file. How would you do that in T-SQL? If your organization has locked down Powershell, I would expect creating files in T-SQL to be an absolute no-no. Maybe SSIS is acceptable middle ground.

Collectives™ on Stack Overflow

Generate scripts for trigger in SQL Server

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related