How to generate sql scripts using SMO scripter

Question

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?

Kodak

Answer 1

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

Avulsion

Answer 2

1. Script all tables without foreign keys, then script out just the foreign keys. You can do this by using the scripting options, and this way the dependencies between tables due to FKs won't matter.
2. Use the DependencyWalker SMO object. You can add the URNs for the views to it and then ask for a linear list of dependencies. My answer here to a similar question contains an example of how to use it.

Moan

Thank you for your answer. I "awarded" the bounty to Renegrin but yours is just as good. I wish there was a way to split the bounty. My solution ended up being different approach for unrelated reason. – Kodak

Answer 3

You could take a different approach and script out tables and then views. Generally in that order, things work, but not 100%.

I would set WithDependencies to false

Slave

WithDependencies does two things: 1) it re-orders scripts by dependency. 2) it includes dependent elements automatically. If I take out WithDependencies, I won't have any problems from #2 but I need to hand-write to achieve #1. That is doable, just want to make sure the expectation is set right for whoever wants to do this. – Kodak

Answer 4

It's admittedly a hack, but you could patch the script before you run it by adding error handling. You could probably leverage the new parser in V3 to automate that.

Sonorant