下面是使用SQLDMO进行数据对象导出的示例：（需要C#版本的请留言，晚上可取）
Imports System.Data.SqlClient Imports SQLDMO Imports System.IO Imports System.Text Module Module1 Sub Main() Dim currentPath As String = String.Empty currentPath = AppDomain.CurrentDomain.BaseDirectory Dim fs As StreamWriter = Nothing Dim di As DirectoryInfo = Nothing Dim server As New SQLDMO.SQLServer() Dim db As SQLDMO.Database2 = Nothing server.Connect("ServerName", "UserID", "Password") For Each d As Database2 In server.Databases If d.Name.ToLower().Equals("Database Name".ToLower()) Then db = d Exit For End If Next di = Directory.CreateDirectory(Path.Combine(currentPath, "Tables")) For Each table As Table2 In db.Tables If Not table.SystemObject Then Dim sql As New StringBuilder sql.AppendLine("/*--Begin Table " & table.Name & "--*/") sql.AppendLine("IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[" & table.Name & "]'))") sql.AppendLine("DROP FUNCTION [dbo].[" & table.Name & "]") sql.AppendLine() sql.AppendLine(table.Script(SQLDMO.SQLDMO_SCRIPT_TYPE.SQLDMOScript_Default, _ String.Empty, _ String.Empty, _ SQLDMO.SQLDMO_SCRIPT2_TYPE.SQLDMOScript2_Default)) sql.AppendLine("/*--End Table " + table.Name + "--*/") fs = New StreamWriter(Path.Combine(di.FullName, table.Name & ".sql")) fs.WriteLine(sql.ToString()) fs.Flush() fs.Close() End If Next di = Directory.CreateDirectory(Path.Combine(currentPath, "StoredProcedures")) For Each sp As StoredProcedure2 In db.StoredProcedures If Not sp.SystemObject Then Dim sql As New StringBuilder sql.AppendLine("/*--Begin Stored Procedure " & sp.Name & "--*/") sql.AppendLine("IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[" & sp.Name & "]'))") sql.AppendLine("DROP FUNCTION [dbo].[" & sp.Name & "]") sql.AppendLine() sql.AppendLine(sp.Script(SQLDMO.SQLDMO_SCRIPT_TYPE.SQLDMOScript_Default, _ String.Empty, _ SQLDMO.SQLDMO_SCRIPT2_TYPE.SQLDMOScript2_Default)) sql.AppendLine("/*--End Stored Procedure " + sp.Name + "--*/") fs = New StreamWriter(Path.Combine(di.FullName, sp.Name & ".sql")) fs.WriteLine(sql.ToString()) fs.Flush() fs.Close() End If Next di = Directory.CreateDirectory(Path.Combine(currentPath, "Views")) For Each view As View2 In db.Views If Not view.SystemObject Then Dim sql As New StringBuilder sql.AppendLine("/*--Begin View " & view.Name & "--*/") sql.AppendLine("IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[" & view.Name & "]'))") sql.AppendLine("DROP FUNCTION [dbo].[" & view.Name & "]") sql.AppendLine() sql.AppendLine(view.Script(SQLDMO.SQLDMO_SCRIPT_TYPE.SQLDMOScript_Default, _ String.Empty, _ SQLDMO.SQLDMO_SCRIPT2_TYPE.SQLDMOScript2_Default)) sql.AppendLine("/*--End View " + view.Name + "--*/") fs = New StreamWriter(Path.Combine(di.FullName, view.Name & ".sql")) fs.WriteLine(sql.ToString()) fs.Flush() fs.Close() End If Next di = Directory.CreateDirectory(Path.Combine(currentPath, "Functions")) For Each func As UserDefinedFunction In db.UserDefinedFunctions If Not func.SystemObject Then Dim sql As New StringBuilder sql.AppendLine("/*--Begin Function " & func.Name & "--*/") sql.AppendLine("IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[" & func.Name & "]'))") sql.AppendLine("DROP FUNCTION [dbo].[" & func.Name & "]") sql.AppendLine() sql.AppendLine(func.Script(SQLDMO.SQLDMO_SCRIPT_TYPE.SQLDMOScript_Default, _ String.Empty, _ SQLDMO.SQLDMO_SCRIPT2_TYPE.SQLDMOScript2_Default)) sql.AppendLine("/*--End Function " + func.Name + "--*/") fs = New StreamWriter(Path.Combine(di.FullName, func.Name & ".sql")) fs.WriteLine(sql.ToString()) fs.Flush() fs.Close() End If Next End Sub End Module
其实是很简单的东西，不知道微软为什么不实现一个简单版本。希望此工具能给大家带来便利，下一篇随笔估计就是如何进行批量.sql文件在数据库中生成数据库对象的实现，感谢大家的支持。
最新评论