How to generate sql scripts using SMO scripter

sql-server powershell smo
kennethc picture kennethc · Oct 17, 2013 · Viewed 10.9k times · Source

My database has tables, views and all. And I need a way to generate SQL script for all the DDL in an automated manner. No need for data.

There are FK constraints so table creation scripts should be ordered properly. Some views use another view, so view creation scripts also have to be ordered properly.

Starting from a script presented on MSDN Blog, I got the following:

function Generate-SqlScript
{
    param(
    [string]$srvname,
    [string]$database,
    [string]$ScriptOutputFileName
    )

    [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | out-null

    $srv =  New-Object ("Microsoft.SqlServer.Management.SMO.Server") ($srvname)

    $allUrns = @()
    $allUrns += $srv.Databases[$database].Tables |  foreach { $_.Urn}
    $allUrns += $srv.Databases[$database].Views |  foreach { $_.Urn}

    $scriptingOptions = New-Object ("Microsoft.SqlServer.Management.SMO.ScriptingOptions") 
    $scriptingOptions.WithDependencies = $true
    $scriptingOptions.AllowSystemObjects = $false
    $scriptingOptions.ToFileOnly = $true
    $scriptingOptions.Permissions = $true
    $scriptingOptions.FileName = "$ScriptOutputFileName"

    $scripter = New-Object ("Microsoft.SqlServer.Management.SMO.Scripter") ($srv)
    $scripter.Options = $scriptingOptions;

    $scripter.Script($allUrns)
}

Generate-SqlScript .\sqlexpress <MyDbName> <FilePath>

Now the problem is, WithDependencies option causes the view script to include its dependent tables, which was already included earlier. If I take out WithDependencies option, generated script does not reflect proper ordering.

So the end result contains all the information, but it is not runnable. It raises an error since it cannot create the table twice.

I find too many posts talking about SMO scripter for this, so I assume there is gotta be something I missed. Or... did all those posts miss this problem?

Answer

Derrick Bell picture Derrick Bell · Oct 23, 2013

You need to discover and sort the tables in dependency order in your PowerShell script before scripting out the objects. Review an implementation of this on the following blog: http://patlau.blogspot.com/2012/09/generate-sqlserver-scripts-with.html

The concept was clearer to me in C#. Check out: http://sqlblog.com/blogs/ben_miller/archive/2007/10/18/scripting-tables-views-and-data-using-smo-part-3.aspx

Related questions

How to use Powershell Where-Object like an IN statement

I have the following code which works: foreach ($db in $svr.Databases | where-object { $_.name -eq "testDB" -or $_.name -eq "master" -or $_.name -eq "model" -or $_.name -eq "msdb" } ) { write-output $db.name } Is a cleaner way to do this? Something like: …

Read More
How to append to powershell Hashtable value?

I am interating through a list of Microsoft.SqlServer.Management.Smo.Server objects and adding them to a hashtable like so: $instances = Get-Content -Path .\Instances.txt $scripts = @{} foreach ($i in $instances) { $instance = New-Object Microsoft.SqlServer.Management.Smo.Server $i foreach($…

Read More
How to automate script generation using SMO in SQL Server?

I would like to automate script generation (in SSMS --> Tasks --> Generate Scripts) in SSMS 2008. I have read that SQL Server 2008 does not support Database Publishing Wizard (including SQLPUBWIZ SCRIPT) but this automation can be done using …

Read More