C#：依赖项的 SMO 问题/Multipe Create Table 语句

Question

我正在尝试使用 SMO 从现有数据库生成 SQL 脚本。稍后将使用此脚本来恢复没有数据的数据库。为此，我设置了一个如下所示的方法：

public void GenerateSQLScripts(string dbName)

    {

        StringBuilder sb = new StringBuilder();

        Server server = new Server(SqlServer);

        Database db = server.Databases[dbName];

        var scriptopt = new ScriptingOptions();

        scriptopt.TargetServerVersion = SqlServerVersion.Version105; // Windows 2008 R2

        scriptopt.AnsiPadding = true;

        scriptopt.WithDependencies = true;

        scriptopt.IncludeHeaders = true;

        scriptopt.SchemaQualify = true;

        scriptopt.ExtendedProperties = true;

        scriptopt.TargetDatabaseEngineType = DatabaseEngineType.Standalone;

        scriptopt.IncludeDatabaseContext = true;

        scriptopt.ScriptDrops = false;

        scriptopt.ScriptData = false;

        scriptopt.ScriptSchema = true;

        scriptopt.DriAllConstraints = true;

        scriptopt.DriForeignKeys = true;

        scriptopt.Indexes = true;

        scriptopt.DriPrimaryKey = true;

        scriptopt.DriUniqueKeys = true;

        scriptopt.DriChecks = true;

        scriptopt.AllowSystemObjects = false;

        scriptopt.AppendToFile = false;

        scriptopt.ScriptBatchTerminator = true;

        // script Tables

        foreach (Table t in db.Tables)

        {

            if (!t.IsSystemObject)

            {

                StringCollection sc = t.Script(scriptopt);

                foreach (string s in sc)

                {

                    sb.AppendLine(s);

                }

            }

        }

此方法创建一个 SQL 文件，其中包含Create Table针对相同表的多个语句。每次引用表时都会发生这种情况，无论它是否存在。我通过关闭 WithDependencies 找到了解决此问题的方法：

scriptopt.WithDependencies = false;

现在脚本要小得多，因为没有额外的语句。但是由于我的语句中没有顺序，因此某些表正在引用当时不存在的其他表。经过一番研究，我找到了DependencyWalker类。

由于测试目的，其余的源代码不受影响。现在我遇到了和以前一样的错误。我的脚本充满了多个Create Table语句，Table already exists即使关闭了该选项也会导致错误。

Answer 1

经过一番研究，我找到了解决我的问题的方法。起初，我将 的值更改DriForeignKeys为false。这会导致外键暂时被排除在脚本之外。在创建表的脚本时，我使用ForeignKeyCollection提取它们的外键：List<ForeignKeyCollection> fkcolList = new List<ForeignKeyCollection>();foreach (Table t in db.Tables)&nbsp; &nbsp; &nbsp; &nbsp; {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; fkcolList.Add(t.ForeignKeys); // Extract the foreign keys&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; if (!t.IsSystemObject)&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; StringCollection sc = t.Script(scriptopt);&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; foreach (string s in sc)&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; sb.AppendLine(s);&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; }&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; }&nbsp; &nbsp; &nbsp; &nbsp; }现在我可以单独编写外键脚本并将其附加到脚本的末尾：&nbsp;foreach (ForeignKeyCollection fkcol in fkcolList) // Generate Relations&nbsp; &nbsp; &nbsp; &nbsp; {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; foreach (ForeignKey fk in fkcol)&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; StringCollection sc = fk.Script();&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; foreach (string s in sc)&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; sb.AppendLine(s);&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; }&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; }&nbsp; &nbsp; &nbsp; &nbsp; }&nbsp; &nbsp; &nbsp; &nbsp; fkcolList.Clear();这可能不是最好的解决方案，因为连续有三个循环，但对我来说它工作得很好。

Answer 2

Smo 是一大笑声，不是吗？你会发现依赖遍历器对每个表产生了明显的冗余引用......因为多重关系......和级联关系。帮自己一个忙，输出依赖walker的树的结果（自己走）……所以你可以看到这是真的。这就是我所做的只是为了“看到”我得到了什么。请注意，缩进说明表被多次提及……当您调用 WalkDependencies 时，这些表只会被压平为“线性列表”……这对您没有任何好处。class Program{&nbsp; static void Main( string[ ] args )&nbsp; {&nbsp; &nbsp; using ( var connection = new SqlConnection( "Data Source=.;Initial Catalog=...;Integrated Security=True" ) )&nbsp; &nbsp; {&nbsp; &nbsp; &nbsp; connection.Open( );&nbsp; &nbsp; &nbsp; var serverConnection = new ServerConnection( connection );&nbsp; &nbsp; &nbsp; var server = new Server( serverConnection );&nbsp; &nbsp; &nbsp; var db = server.Databases[ "..." ];&nbsp; &nbsp; &nbsp; var objects = new UrnCollection( );&nbsp; &nbsp; &nbsp; foreach ( Table table in db.Tables )&nbsp; &nbsp; &nbsp; {&nbsp; &nbsp; &nbsp; &nbsp; objects.Add( table.Urn );&nbsp; &nbsp; &nbsp; }&nbsp; &nbsp; &nbsp; var dependency = new DependencyWalker( server );&nbsp; &nbsp; &nbsp; var tree = dependency.DiscoverDependencies( objects, DependencyType.Parents );&nbsp; &nbsp; &nbsp; Walk( tree.FirstChild );&nbsp; &nbsp; }&nbsp; }&nbsp; static void Walk( DependencyTreeNode node, int depth = 0 )&nbsp; {&nbsp; &nbsp; Print( node.Urn, depth );&nbsp; &nbsp; if ( node.HasChildNodes )&nbsp; &nbsp; {&nbsp; &nbsp; &nbsp; Walk( node.FirstChild, depth + 1 );&nbsp; &nbsp; }&nbsp; &nbsp; if ( node.NextSibling != null )&nbsp; &nbsp; {&nbsp; &nbsp; &nbsp; Walk( node.NextSibling, depth );&nbsp; &nbsp; }&nbsp; }&nbsp; static void Print( string message, int depth )&nbsp; {&nbsp; &nbsp; var space = string.Empty;&nbsp; &nbsp; for ( int i = 0; i < depth; i++ ) space += " ";&nbsp; &nbsp; Debug.WriteLine( string.Format( "{0}{1}", space, message ) );&nbsp; }}我在一个带有一组名义外键的小型规范化数据库上运行了上述代码。正是这些外键导致给定表的多次提及。这是部分编辑的输出：Server[@Name='...']/Database[@Name='...']/Table[@Name='AddressTypes' and @Schema='dbo']Server[@Name='...']/Database[@Name='...']/Table[@Name='ApplicationComponents' and @Schema='dbo']&nbsp;Server[@Name='...']/Database[@Name='...']/UserDefinedFunction[@Name='GetApplicationElementExtension' and @Schema='dbo']&nbsp; Server[@Name='...']/Database[@Name='...']/Table[@Name='ApplicationElements' and @Schema='dbo']&nbsp;Server[@Name='...']/Database[@Name='...']/UserDefinedFunction[@Name='GetApplicationElementName' and @Schema='dbo']&nbsp; Server[@Name='...']/Database[@Name='...']/Table[@Name='ApplicationElements' and @Schema='dbo']&nbsp;Server[@Name='...']/Database[@Name='...']/UserDefinedFunction[@Name='GetArchitecture' and @Schema='dbo']&nbsp; Server[@Name='...']/Database[@Name='...']/Table[@Name='Architectures' and @Schema='dbo']&nbsp;Server[@Name='...']/Database[@Name='...']/UserDefinedFunction[@Name='GetArchitectureName' and @Schema='dbo']&nbsp; Server[@Name='...']/Database[@Name='...']/Table[@Name='Architectures' and @Schema='dbo']&nbsp;Server[@Name='...']/Database[@Name='...']/UserDefinedFunction[@Name='GetSetting' and @Schema='dbo']&nbsp; Server[@Name='...']/Database[@Name='...']/Table[@Name='Settings' and @Schema='dbo']&nbsp;Server[@Name='...']/Database[@Name='...']/Table[@Name='ApplicationElements' and @Schema='dbo']Server[@Name='...']/Database[@Name='...']/Table[@Name='ApplicationElements' and @Schema='dbo']Server[@Name='...']/Database[@Name='...']/Table[@Name='Architectures' and @Schema='dbo']Server[@Name='...']/Database[@Name='...']/Table[@Name='DataFiles' and @Schema='dbo']&nbsp;Server[@Name='...']/Database[@Name='...']/Sequence[@Name='FileSequence' and @Schema='dbo']Server[@Name='...']/Database[@Name='...']/Table[@Name='DirectoryEntries' and @Schema='dbo']&nbsp;Server[@Name='...']/Database[@Name='...']/Table[@Name='Lists' and @Schema='dbo']&nbsp; Server[@Name='...']/Database[@Name='...']/Sequence[@Name='ListSequence' and @Schema='dbo']&nbsp;Server[@Name='...']/Database[@Name='...']/Table[@Name='Names' and @Schema='dbo']&nbsp; Server[@Name='...']/Database[@Name='...']/Sequence[@Name='NameSequence' and @Schema='dbo']&nbsp;Server[@Name='...']/Database[@Name='...']/Table[@Name='SecurityIds' and @Schema='dbo']&nbsp; Server[@Name='...']/Database[@Name='...']/Sequence[@Name='SecuritySequence' and @Schema='dbo']&nbsp;Server[@Name='...']/Database[@Name='...']/Sequence[@Name='DirectoryEntrySequence' and @Schema='dbo']Server[@Name='...']/Database[@Name='...']/Table[@Name='Images' and @Schema='dbo']&nbsp;Server[@Name='...']/Database[@Name='...']/Table[@Name='Snapshots' and @Schema='dbo']&nbsp; Server[@Name='...']/Database[@Name='...']/Table[@Name='Machines' and @Schema='dbo']&nbsp; &nbsp;Server[@Name='...']/Database[@Name='...']/Table[@Name='Architectures' and @Schema='dbo']&nbsp; Server[@Name='...']/Database[@Name='...']/Sequence[@Name='SnapshotSequence' and @Schema='dbo']&nbsp; Server[@Name='...']/Database[@Name='...']/Table[@Name='Progress' and @Schema='dbo']&nbsp;Server[@Name='...']/Database[@Name='...']/Table[@Name='Progress' and @Schema='dbo']&nbsp;Server[@Name='...']/Database[@Name='...']/Table[@Name='Architectures' and @Schema='dbo']Server[@Name='...']/Database[@Name='...']/Table[@Name='KeyTypes' and @Schema='dbo']Server[@Name='...']/Database[@Name='...']/Table[@Name='ListItems' and @Schema='dbo']&nbsp;Server[@Name='...']/Database[@Name='...']/Table[@Name='Streams' and @Schema='dbo']&nbsp; Server[@Name='...']/Database[@Name='...']/Table[@Name='DataFiles' and @Schema='dbo']&nbsp; &nbsp;Server[@Name='...']/Database[@Name='...']/Sequence[@Name='FileSequence' and @Schema='dbo']&nbsp;Server[@Name='...']/Database[@Name='...']/Table[@Name='Lists' and @Schema='dbo']&nbsp; Server[@Name='...']/Database[@Name='...']/Sequence[@Name='ListSequence' and @Schema='dbo']Server[@Name='...']/Database[@Name='...']/Table[@Name='Lists' and @Schema='dbo']&nbsp;Server[@Name='...']/Database[@Name='...']/Sequence[@Name='ListSequence' and @Schema='dbo']Server[@Name='...']/Database[@Name='...']/Table[@Name='MachineAddresses' and @Schema='dbo']&nbsp;Server[@Name='...']/Database[@Name='...']/Table[@Name='Machines' and @Schema='dbo']&nbsp; Server[@Name='...']/Database[@Name='...']/Table[@Name='Architectures' and @Schema='dbo']&nbsp;Server[@Name='...']/Database[@Name='...']/Table[@Name='AddressTypes' and @Schema='dbo']Server[@Name='...']/Database[@Name='...']/Table[@Name='MachineKeys' and @Schema='dbo']&nbsp;Server[@Name='...']/Database[@Name='...']/Table[@Name='Machines' and @Schema='dbo']&nbsp; Server[@Name='...']/Database[@Name='...']/Table[@Name='Architectures' and @Schema='dbo']&nbsp;Server[@Name='...']/Database[@Name='...']/Sequence[@Name='MachineKeySequence' and @Schema='dbo']&nbsp;Server[@Name='...']/Database[@Name='...']/Table[@Name='KeyTypes' and @Schema='dbo']Server[@Name='...']/Database[@Name='...']/Table[@Name='Machines' and @Schema='dbo']&nbsp;Server[@Name='...']/Database[@Name='...']/Table[@Name='Architectures' and @Schema='dbo']Server[@Name='...']/Database[@Name='...']/Table[@Name='Names' and @Schema='dbo']&nbsp;Server[@Name='...']/Database[@Name='...']/Sequence[@Name='NameSequence' and @Schema='dbo']Server[@Name='...']/Database[@Name='...']/Table[@Name='Progress' and @Schema='dbo']Server[@Name='...']/Database[@Name='...']/Table[@Name='SecurityIds' and @Schema='dbo']&nbsp;Server[@Name='...']/Database[@Name='...']/Sequence[@Name='SecuritySequence' and @Schema='dbo']Server[@Name='...']/Database[@Name='...']/Table[@Name='Settings' and @Schema='dbo']Server[@Name='...']/Database[@Name='...']/Table[@Name='SnapshotDirectoryEntries' and @Schema='dbo']&nbsp;Server[@Name='...']/Database[@Name='...']/Table[@Name='Snapshots' and @Schema='dbo']&nbsp; Server[@Name='...']/Database[@Name='...']/Table[@Name='Machines' and @Schema='dbo']&nbsp; &nbsp;Server[@Name='...']/Database[@Name='...']/Table[@Name='Architectures' and @Schema='dbo']&nbsp; Server[@Name='...']/Database[@Name='...']/Sequence[@Name='SnapshotSequence' and @Schema='dbo']&nbsp; Server[@Name='...']/Database[@Name='...']/Table[@Name='Progress' and @Schema='dbo']&nbsp;Server[@Name='...']/Database[@Name='...']/Table[@Name='DirectoryEntries' and @Schema='dbo']&nbsp; Server[@Name='...']/Database[@Name='...']/Table[@Name='Lists' and @Schema='dbo']&nbsp; &nbsp;Server[@Name='...']/Database[@Name='...']/Sequence[@Name='ListSequence' and @Schema='dbo']&nbsp; Server[@Name='...']/Database[@Name='...']/Table[@Name='Names' and @Schema='dbo']&nbsp; &nbsp;Server[@Name='...']/Database[@Name='...']/Sequence[@Name='NameSequence' and @Schema='dbo']&nbsp; Server[@Name='...']/Database[@Name='...']/Table[@Name='SecurityIds' and @Schema='dbo']&nbsp; &nbsp;Server[@Name='...']/Database[@Name='...']/Sequence[@Name='SecuritySequence' and @Schema='dbo']&nbsp; Server[@Name='...']/Database[@Name='...']/Sequence[@Name='DirectoryEntrySequence' and @Schema='dbo']Server[@Name='...']/Database[@Name='...']/Table[@Name='Snapshots' and @Schema='dbo']&nbsp;Server[@Name='...']/Database[@Name='...']/Table[@Name='Machines' and @Schema='dbo']&nbsp; Server[@Name='...']/Database[@Name='...']/Table[@Name='Architectures' and @Schema='dbo']&nbsp;Server[@Name='...']/Database[@Name='...']/Sequence[@Name='SnapshotSequence' and @Schema='dbo']&nbsp;Server[@Name='...']/Database[@Name='...']/Table[@Name='Progress' and @Schema='dbo']Server[@Name='...']/Database[@Name='...']/Table[@Name='Streams' and @Schema='dbo']&nbsp;Server[@Name='...']/Database[@Name='...']/Table[@Name='DataFiles' and @Schema='dbo']&nbsp; Server[@Name='...']/Database[@Name='...']/Sequence[@Name='FileSequence' and @Schema='dbo']快速扫描会发现某些表被引用了六七次。在好转之前它会变得更糟。你最终要做的就是找出独立的东西......可以首先编写脚本的东西。这些东西没有依赖关系。然后，将这些独立的项目放在一个列表中并一遍又一遍地运行依赖项walker，将内容添加到您的列表中……其中项目仅依赖于您已经添加的项目。因此，您将拥有可编写脚本的订单。请注意，就地数据库在技术上仍然可能以这种方式无法解决。表 a 可以有一个表 b 的引用，表 b 有一个表 a 的引用。这是完全合法的（即使不受欢迎）。我更喜欢做的是使用 Visual Studio 来完成艰苦的工作：从这里为 Visual Studio 添加 SQL Server Data Tools创建一个数据库项目。导入您现有的数据库。Build+Publish 菜单生成脚本。它可以做一些神奇的事情……比如创建对象，然后稍后修改它们以处理 a->b->a 类型的事情。您还可以发布到现有数据库，它会小心地生成一个仅进行更改的脚本。这很棒。

Answer 3

您可能不需要手动遍历任何内容。只需将脚本程序配置为包含您想要的类型（表、存储过程、所有者、甚至数据）并运行它，它将处理依赖项（至少它为我生成了一个 DataOnly 脚本）：var server = new Server(@".\SQLExpress");var database = server.Databases["mydb"];var scripter = new Scripter(server);//scripter.Options.WithDependencies = true; //didn't even need this optionscripter.Options.ScriptData = true;scripter.Options.ScriptSchema = false;var tables = database.Tables.Cast<Table>().Where(t => !t.IsSystemObject).ToList();var scripts = scripter.EnumScriptWithList(tables.Select(t => t.Urn).ToArray());return string.Join("\n", scripts.Select(s => s));