如何使用 SMO 脚本程序生成 sql 脚本 [英] How to generate sql scripts using SMO scripter

问题描述

我的数据库有表格、视图等等.我需要一种以自动化方式为所有 DDL 生成 SQL 脚本的方法.无需数据.

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.

存在 FK 约束，因此表创建脚本应正确排序.一些视图使用另一个视图，因此视图创建脚本也必须正确排序.

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.

从 MSDN 博客，我得到了以下内容:

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>

现在的问题是，WithDependencies 选项会导致视图脚本包含之前已经包含的依赖表.如果我去掉 WithDependencies 选项，生成的脚本不会反映正确的顺序.

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.

所以最终结果包含所有信息，但它是不可运行的.它会引发错误，因为它无法创建两次表.

为此，我发现有太多关于 SMO 脚本编写器的帖子，所以我想一定有我错过的东西.或者……所有这些帖子都忽略了这个问题吗?

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?

推荐答案

在编写对象脚本之前，您需要在 PowerShell 脚本中按依赖关系顺序发现表并对其进行排序.在以下博客中查看此实现:http://patlau.blogspot.com/2012/09/generate-sqlserver-scripts-with.html

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

这个概念在 C# 中对我来说更清晰.查看:http://sqlblog.com/blogs/ben_miller/archive/2007/10/18/scripting-tables-views-and-data-using-smo-part-3.aspx

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

这篇关于如何使用 SMO 脚本程序生成 sql 脚本的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！