asked
Have SMO generate scripts instead of actually creating or altering database
Hi, I know I've done this before, but I can't remember which property to set where to achieve it... I'm using Powershell and SMO to create some objects (triggers in this case), based on configuration files. I'm more or less with the logic of the Powershell scripts, but now to my issue. On the devbox, I want to run this Powershell script, and instead of actually executing my Create, Alter and Drop statements on the machine, I want to have SMO record them and finish by scripting out the statements. So if I have created a trigger in the Powershell-script and end that portion of the code with $mytriggerobject.Create, I want SMO to store the DDL of the Create Trigger statement instead of executing it and actually creating the trigger. That way I can generate DDL-scripts which I can include in our installation scripts instead of having to distribute out the Powershell scripts and configuration files, get them in the right place and execute Powershell scripts on production servers. I know I did this when I was about to alter a few thousand stored procedures and change every varchar variable and varchar string value to nvarchar in a database. Then I used SMO to alter Stored Procedures, but instead of just executing the Alter statements, I found an option in SMO which would have the Alter-statements recorded to a string collection, which I could use to write out all the Alter statements and review them before applying them.
And 20 minutes later, I dug out some old code from a repository I had forgotten that I had saved on my OneDrive and found the answer. Figure I'd share it with you. On the server-object in SMO, there's a ConnectionContext object with a SqlExecutionModes property. With this property set to CaptureSql, all DDL-statements which SMO generates are only captured, instead of captured and executed. Also on the ConnectionContext of the server object, there's a string collection CapturedSql, which contains all statements generated. With code: [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null $srv = New-Object("Microsoft.SqlServer.Management.SMO.Server") $servername; $srv.ConnectionContext.SqlExecutionModes = [Microsoft.SqlServer.Management.Common.SqlExecutionModes]::CaptureSql $db = $srv.databases.Item("dbname") $tab=$db.Tables | Where-Objecct{$_.schema -eq "schemaname" -and $_.name -eq "tablename"} $tr = New-Object -TypeName Microsoft.SqlServer.Management.SMO.Trigger -argumentlist $tab,$triggername $tr.TextMode=$false $tr.Insert=$true $tr.Update=$false $tr.InsertOrder = [Microsoft.SqlServer.Management.SMO.Agent.ActivationOrder]::First $tr..ImplementationType=[Microsoft.SqlServer.Management.SMO.ImplementationType]::TransactSql $tr.TextBody="PRINT 1" $tr.Create() foreach($s in $srv.ConnectionContext.CapturedSql){ Write-Host $s.Text Write-Host "GO" } Still struggling with the formatting of newlines in the captured SQL, but at least this the basics of it.
16 People are following this question.
