我正在嘗試使用 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類。由于測試目的，其余的源代碼不受影響?，F在我遇到了和以前一樣的錯誤。我的腳本充滿了多個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();
這可能不是最好的解決方案，因為連續有三個循環，但對我來說它工作得很好。
吃雞游戲
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='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='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']
Server[@Name='...']/Database[@Name='...']/Table[@Name='KeyTypes' 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='Names' and @Schema='dbo']
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']
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']
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='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='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='Streams' and @Schema='dbo']
Server[@Name='...']/Database[@Name='...']/Table[@Name='DataFiles' and @Schema='dbo']
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 類型的事情。您還可以發布到現有數據庫，它會小心地生成一個僅進行更改的腳本。這很棒。
墨色風雨
TA貢獻1853條經驗 獲得超6個贊
您可能不需要手動遍歷任何內容。只需將腳本程序配置為包含您想要的類型（表、存儲過程、所有者、甚至數據）并運行它，它將處理依賴項（至少它為我生成了一個 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 option
scripter.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));
- 3 回答
- 0 關注
- 235 瀏覽
添加回答
舉報
0/150
提交
取消