首页猿问 C#：依赖项的 SMO...

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

Cats萌萌 2021-12-25 16:59:31

我正在尝试使用 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即使关闭了该选项也会导致错误。

查看完整描述

3 回答

MMMHUHU

TA贡献1834条经验获得超8个赞

经过一番研究，我找到了解决我的问题的方法。起初，我将的值更改DriForeignKeys为false。这会导致外键暂时被排除在脚本之外。在创建表的脚本时，我使用ForeignKeyCollection提取它们的外键：

List<ForeignKeyCollection> fkcolList = new List<ForeignKeyCollection>();

foreach (Table t in db.Tables)

{

fkcolList.Add(t.ForeignKeys); // Extract the foreign keys

if (!t.IsSystemObject)

{

StringCollection sc = t.Script(scriptopt);

foreach (string s in sc)

{

sb.AppendLine(s);

}

现在我可以单独编写外键脚本并将其附加到脚本的末尾：

foreach (ForeignKeyCollection fkcol in fkcolList) // Generate Relations

{

foreach (ForeignKey fk in fkcol)

{

StringCollection sc = fk.Script();

foreach (string s in sc)

{

sb.AppendLine(s);

}

fkcolList.Clear();

这可能不是最好的解决方案，因为连续有三个循环，但对我来说它工作得很好。

反对回复 2021-12-25

吃鸡游戏

TA贡献1829条经验获得超7个赞

Smo 是一大笑声，不是吗？你会发现依赖遍历器对每个表产生了明显的冗余引用......因为多重关系......和级联关系。

帮自己一个忙，输出依赖walker的树的结果（自己走）……所以你可以看到这是真的。这就是我所做的只是为了“看到”我得到了什么。请注意，缩进说明表被多次提及……当您调用 WalkDependencies 时，这些表只会被压平为“线性列表”……这对您没有任何好处。

class Program

{

static void Main( string[ ] args )

{

using ( var connection = new SqlConnection( "Data Source=.;Initial Catalog=...;Integrated Security=True" ) )

{

connection.Open( );

var serverConnection = new ServerConnection( connection );

var server = new Server( serverConnection );

var db = server.Databases[ "..." ];

var objects = new UrnCollection( );

foreach ( Table table in db.Tables )

{

objects.Add( table.Urn );

}

var dependency = new DependencyWalker( server );

var tree = dependency.DiscoverDependencies( objects, DependencyType.Parents );

Walk( tree.FirstChild );

}

static void Walk( DependencyTreeNode node, int depth = 0 )

{

Print( node.Urn, depth );

if ( node.HasChildNodes )

{

Walk( node.FirstChild, depth + 1 );

}

if ( node.NextSibling != null )

{

Walk( node.NextSibling, depth );

}

static void Print( string message, int depth )

{

var space = string.Empty;

for ( int i = 0; i < depth; i++ ) space += " ";

Debug.WriteLine( string.Format( "{0}{1}", space, message ) );

}

我在一个带有一组名义外键的小型规范化数据库上运行了上述代码。正是这些外键导致给定表的多次提及。

这是部分编辑的输出：

Server[@Name='...']/Database[@Name='...']/Table[@Name='AddressTypes' and @Schema='dbo']

Server[@Name='...']/Database[@Name='...']/Table[@Name='ApplicationComponents' and @Schema='dbo']

Server[@Name='...']/Database[@Name='...']/UserDefinedFunction[@Name='GetApplicationElementExtension' and @Schema='dbo']

Server[@Name='...']/Database[@Name='...']/Table[@Name='ApplicationElements' and @Schema='dbo']

Server[@Name='...']/Database[@Name='...']/UserDefinedFunction[@Name='GetApplicationElementName' and @Schema='dbo']

Server[@Name='...']/Database[@Name='...']/Table[@Name='ApplicationElements' and @Schema='dbo']

Server[@Name='...']/Database[@Name='...']/UserDefinedFunction[@Name='GetArchitecture' and @Schema='dbo']

Server[@Name='...']/Database[@Name='...']/Table[@Name='Architectures' and @Schema='dbo']

Server[@Name='...']/Database[@Name='...']/UserDefinedFunction[@Name='GetArchitectureName' and @Schema='dbo']

Server[@Name='...']/Database[@Name='...']/Table[@Name='Architectures' and @Schema='dbo']

Server[@Name='...']/Database[@Name='...']/UserDefinedFunction[@Name='GetSetting' and @Schema='dbo']

Server[@Name='...']/Database[@Name='...']/Table[@Name='Settings' 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']

Server[@Name='...']/Database[@Name='...']/Sequence[@Name='FileSequence' and @Schema='dbo']

Server[@Name='...']/Database[@Name='...']/Table[@Name='DirectoryEntries' and @Schema='dbo']

Server[@Name='...']/Database[@Name='...']/Table[@Name='Lists' and @Schema='dbo']

Server[@Name='...']/Database[@Name='...']/Sequence[@Name='ListSequence' and @Schema='dbo']

Server[@Name='...']/Database[@Name='...']/Table[@Name='Names' and @Schema='dbo']

Server[@Name='...']/Database[@Name='...']/Sequence[@Name='NameSequence' and @Schema='dbo']

Server[@Name='...']/Database[@Name='...']/Table[@Name='SecurityIds' and @Schema='dbo']

Server[@Name='...']/Database[@Name='...']/Sequence[@Name='SecuritySequence' and @Schema='dbo']

Server[@Name='...']/Database[@Name='...']/Sequence[@Name='DirectoryEntrySequence' and @Schema='dbo']

Server[@Name='...']/Database[@Name='...']/Table[@Name='Images' and @Schema='dbo']

Server[@Name='...']/Database[@Name='...']/Table[@Name='Snapshots' and @Schema='dbo']

Server[@Name='...']/Database[@Name='...']/Table[@Name='Machines' and @Schema='dbo']

Server[@Name='...']/Database[@Name='...']/Table[@Name='Architectures' and @Schema='dbo']

Server[@Name='...']/Database[@Name='...']/Sequence[@Name='SnapshotSequence' and @Schema='dbo']

Server[@Name='...']/Database[@Name='...']/Table[@Name='Progress' and @Schema='dbo']

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']

Server[@Name='...']/Database[@Name='...']/Table[@Name='Streams' and @Schema='dbo']

Server[@Name='...']/Database[@Name='...']/Table[@Name='DataFiles' and @Schema='dbo']

Server[@Name='...']/Database[@Name='...']/Sequence[@Name='FileSequence' and @Schema='dbo']

Server[@Name='...']/Database[@Name='...']/Table[@Name='Lists' and @Schema='dbo']

Server[@Name='...']/Database[@Name='...']/Sequence[@Name='ListSequence' and @Schema='dbo']

Server[@Name='...']/Database[@Name='...']/Table[@Name='Lists' and @Schema='dbo']

Server[@Name='...']/Database[@Name='...']/Sequence[@Name='ListSequence' and @Schema='dbo']

Server[@Name='...']/Database[@Name='...']/Table[@Name='MachineAddresses' and @Schema='dbo']

Server[@Name='...']/Database[@Name='...']/Table[@Name='Machines' and @Schema='dbo']

Server[@Name='...']/Database[@Name='...']/Table[@Name='Architectures' and @Schema='dbo']

Server[@Name='...']/Database[@Name='...']/Table[@Name='AddressTypes' and @Schema='dbo']

Server[@Name='...']/Database[@Name='...']/Table[@Name='MachineKeys' and @Schema='dbo']

Server[@Name='...']/Database[@Name='...']/Table[@Name='Machines' and @Schema='dbo']

Server[@Name='...']/Database[@Name='...']/Table[@Name='Architectures' and @Schema='dbo']

Server[@Name='...']/Database[@Name='...']/Sequence[@Name='MachineKeySequence' and @Schema='dbo']