' Write scripts using Microsoft Visual Basic 2008.
' The ScriptMain is the entry point class of the script.
Imports System
Imports System.Data
Imports System.Math
Imports System.IO
' compile with:
' /r:Microsoft.SqlServer.Smo.dll
' /r:Microsoft.SqlServer.ConnectionInfo.dll
' /r:Microsoft.SqlServer.Management.Sdk.Sfc.dll
Imports Microsoft.SqlServer.Dts.Runtime
Imports Microsoft.SqlServer.Management.Smo
Imports Microsoft.SqlServer.Management.Common
Imports System.Collections.Specialized
Imports Microsoft.SqlServer.Management.Sdk.Sfc
Partial Public Class ScriptMain
Inherits Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
Enum ScriptResults
Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success
Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
End Enum
' The execution engine calls this method when the task executes.
' To access the object model, use the Dts property. Connections, variables, events,
' and logging features are available as members of the Dts property as shown in the following examples.
'
' To reference a variable, call Dts.Variables("MyCaseSensitiveVariableName").Value
' To post a log entry, call Dts.Log("This is my log text", 999, Nothing)
' To fire an event, call Dts.Events.FireInformation(99, "test", "hit the help message", "", 0, True)
'
' To use the connections collection use something like the following:
' ConnectionManager cm = Dts.Connections.Add("OLEDB")
' cm.ConnectionString = "Data Source=localhost;Initial Catalog=AdventureWorks;Provider=SQLNCLI10;Integrated Security=SSPI;Auto Translate=False;"
'
' Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
'
' To open Help, press F1.
Public Sub Main()
'
' Add your code here
'
CreateDBScript()
Dts.TaskResult = ScriptResults.Success
End Sub
Public Sub CreateDBScript()
' Connect to the local, default instance of SQL Server.
Dim srv As New Server(Dts.Variables("DBInstanceName").Value.ToString)
'Get DB's to script
Dim scDB() As String
scDB = Split(Dts.Variables("DBToScript").Value.ToString, ";")
'MsgBox(Dts.Variables("DBToScript").Value.ToString)
'Open the Script File
Dim scrDB As New StreamWriter(Dts.Variables("ScriptOutputFileName").Value.ToString, False)
'Get all the databases
Dim scrp As New Scripter(srv)
scrDB.WriteLine("-- Scripting databases for " + Dts.Variables("DBInstanceName").Value.ToString)
For Each dbn As Database In srv.Databases
If dbn.IsSystemObject = False Then
Dim sc As System.Collections.Specialized.StringCollection = dbn.Script
For Each st As String In sc
scrDB.WriteLine(st)
Next
scrDB.WriteLine("--")
scrDB.WriteLine("")
End If
Next
'Loop the array
For i As Integer = scDB.GetLowerBound(0) To scDB.GetUpperBound(0)
' database name
Dim dbName As [String] = scDB(i) ' database name
' Reference the database.
Dim db As Database = srv.Databases(dbName)
' Define a Scripter object and set the required scripting options.
scrp.Options.ScriptDrops = False
scrp.Options.WithDependencies = True
scrp.Options.Indexes = True ' To include indexes
scrp.Options.DriAllConstraints = True ' to include referential constraints in the script
scrp.Options.SchemaQualify = True
scrp.Options.ExtendedProperties = True
scrDB.WriteLine("-- Scripting schemas for " + dbName)
For Each sch As Schema In db.Schemas
If sch.IsSystemObject = False Then
' Generating script for schema sch
Dim sc As System.Collections.Specialized.StringCollection = sch.Script
For Each st As String In sc
scrDB.WriteLine(st)
Next
scrDB.WriteLine("--")
scrDB.WriteLine("")
End If
Next
scrp.Options.WithDependencies = True
' Iterate through the tables in database and script each one. Display the script.
For Each tb As Table In db.Tables
' check if the table is not a system table
If tb.IsSystemObject = False Then
scrDB.WriteLine("-- Scripting for table " + tb.Name)
' Generating script for table tb
Dim sc As System.Collections.Specialized.StringCollection = scrp.Script(New Urn() {tb.Urn})
For Each st As String In sc
scrDB.WriteLine(st)
Next
scrDB.WriteLine("--")
scrDB.WriteLine("")
End If
Next
scrp.Options.WithDependencies = False
' Iterate through the views in database and script each one. Display the script.
For Each vw As View In db.Views
' check if the table is not a system table
If vw.IsSystemObject = False Then
scrDB.WriteLine("-- Scripting for view " + vw.Name)
' Generating script for table tb
Dim sc As System.Collections.Specialized.StringCollection = scrp.Script(New Urn() {vw.Urn})
For Each st As String In sc
scrDB.WriteLine(st)
Next
scrDB.WriteLine("--")
scrDB.WriteLine("")
End If
Next
Next i
scrDB.Close()
End Sub
End Class
Posts
