Private Sub ScriptAllObjects()
Dim objSQLServer As SQLOLE.SQLServer
'------------------------------------------------------
'--- Create SQL-DMO's SQLServer ActiveX object.
'------------------------------------------------------
Set objSQLServer = New SQLOLE.SQLServer
If objSQLServer.Status <> SQLOLESvc_Running Then
'------------------------------------------------------
'--- If SQL Server is not running, then we start it.
'--- and connect to it.
'------------------------------------------------------
objSQLServer.Start cSQLServer, cSQLServerUserName, cSQLServerPassword
Else
'------------------------------------------------------
'--- If SQL Server is running, we connect to it.
'------------------------------------------------------
objSQLServer.Connect cSQLServer, cSQLServerUserName, cSQLServerPassword
End If
'------------------------------------------------------
'--- Make an object reference to the pubs database.
'------------------------------------------------------
Set mobjDatabase = objSQLServer.Databases(cDatabaseName)
'------------------------------------------------------
'--- Script all of the main database objects.
'------------------------------------------------------
CreatePath
ScriptTables
ScriptViews
ScriptStoredProcedures
ScriptRules
ScriptDefaults
ScriptUserDefinedDatatypes
'------------------------------------------------------
'--- Close the connection to SQL Server and
'--- destroy the object reference.
'------------------------------------------------------
objSQLServer.Close
Set objSQLServer = Nothing
End Sub
Figure 3 ScriptTables
Private Sub ScriptTables()
Dim i As Integer
Dim strData As String
'------------------------------------------------------
'--- Open a handle to a text file so we can write
'--- our database script to it.
'------------------------------------------------------
Open cScriptingPath & "Tables.txt" For Output As #1
With mobjDatabase.Tables
'------------------------------------------------------
'--- Loop through each Table and write the DROP
'--- and CREATE script for each Table to our text file.
'------------------------------------------------------
For i = 1 To .Count
strData = .Item(i).Script(SQLOLEScript_Default + SQLOLEScript_Drops + SQLOLEScript_Triggers)
Print #1, strData
Next
End With
Close #1
End Sub
Figure 4 Other Scripting Routines
Private Sub ScriptViews()
Dim i As Integer
Dim strData As String
'------------------------------------------------------
'--- Open a handle to a text file so we can write
'--- our database script to it.
'------------------------------------------------------
Open cScriptingPath & "Views.txt" For Output As #1
With mobjDatabase.Views
'------------------------------------------------------
'--- Loop through each Views and write the DROP
'--- and CREATE script for each Views to our text file.
'------------------------------------------------------
For i = 1 To .Count
strData = .Item(i).Script(SQLOLEScript_Default + SQLOLEScript_Drops)
Print #1, strData
Next
End With
Close #1
End Sub
Private Sub ScriptStoredProcedures()
Dim i As Integer
Dim strData As String
'------------------------------------------------------
'--- Open a handle to a text file so we can write
'--- our database script to it.
'------------------------------------------------------
Open cScriptingPath & "StoredProcedures.txt" For Output As #1
With mobjDatabase.StoredProcedures
'------------------------------------------------------
'--- Loop through each Stored Procedure and write the
'--- DROP and CREATE script for each Stored Procedure
'--- to our text file.
'------------------------------------------------------
For i = 1 To .Count
strData = .Item(i).Script(SQLOLEScript_Default + SQLOLEScript_Drops)
Print #1, strData
Next
End With
Close #1
End Sub
Private Sub ScriptRules()
Dim i As Integer
Dim strData As String
'------------------------------------------------------
'--- Open a handle to a text file so we can write
'--- our database script to it.
'------------------------------------------------------
Open cScriptingPath & "Rules.txt" For Output As #1
With mobjDatabase.Rules
'------------------------------------------------------
'--- Loop through each Rules and write the DROP
'--- and CREATE script for each Rules to our text file.
'------------------------------------------------------
For i = 1 To .Count
strData = .Item(i).Script(SQLOLEScript_Default + SQLOLEScript_Drops)
Print #1, strData
Next
End With
Close #1
End Sub
Private Sub ScriptDefaults()
Dim i As Integer
Dim strData As String
'------------------------------------------------------
'--- Open a handle to a text file so we can write
'--- our database script to it.
'------------------------------------------------------
Open cScriptingPath & "Defaults.txt" For Output As #1
With mobjDatabase.Defaults
'------------------------------------------------------
'--- Loop through each Default and write the DROP and
'--- CREATE script for each Default to our text file.
'------------------------------------------------------
For i = 1 To .Count
strData = .Item(i).Script(SQLOLEScript_Default + SQLOLEScript_Drops)
Print #1, strData
Next
End With
Close #1
End Sub
Private Sub ScriptUserDefinedDatatypes()
Dim i As Integer
Dim strData As String
'------------------------------------------------------
'--- Open a handle to a text file so we can write
'--- our database script to it.
'------------------------------------------------------
Open cScriptingPath & "UDTs.txt" For Output As #1
With mobjDatabase.UserDefinedDatatypes
'------------------------------------------------------
'--- Loop through each UDT and write the DROP
'--- and CREATE script for each UDT to our text file.
'------------------------------------------------------
For i = 1 To .Count
strData = .Item(i).Script(SQLOLEScript_Default + SQLOLEScript_Drops)
Print #1, strData
Next
End With
Close #1
End Sub
Figure 6 CheckProjectInAndOut
Private Sub CheckProjectInAndOut()
Dim objSourceSafe As SourceSafeTypeLib.VSSDatabase
Dim objVSSProject As VSSItem
Dim strProject As String
'------------------------------------------------------
'--- Create the SourceSafe ActiveX object.
'------------------------------------------------------
Set objSourceSafe = New SourceSafeTypeLib.VSSDatabase
'------------------------------------------------------
'--- Open a connection to the SourceSafe database
'--- using the srcsafe.ini file and a valid
'--- userid/password combination.
'------------------------------------------------------
objSourceSafe.Open cSourceSafeINI, cVSSUserName, cVSSPassword
'------------------------------------------------------
'--- Create the project path of $/PubsScripts/
'------------------------------------------------------
strProject = cVSSRootProject & cVSSSubProject
'------------------------------------------------------
'--- Set the project for the database script files.
'--- (Project is expected to be something like:
'--- $/RootProject/SubProject)
'------------------------------------------------------
On Error Resume Next
Set objVSSProject = objSourceSafe.VSSItem(strProject)
'------------------------------------------------------
'--- If the project does not exist...
'------------------------------------------------------
If Err <> 0 Then
'------------------------------------------------------
'--- Create the project structure.
'------------------------------------------------------
objSourceSafe.VSSItem("$/").NewSubproject cVSSSubProject
Set objVSSProject = objSourceSafe.VSSItem(strProject)
'------------------------------------------------------
'--- Add the scripting files to the project.
'------------------------------------------------------
objVSSProject.Add cScriptingPath, "All script files"
End If
On Error GoTo 0
'------------------------------------------------------
'--- Check the files into SourceSafe.
'------------------------------------------------------
objVSSProject.Checkin
'------------------------------------------------------
'--- Check the files back out of SourceSafe.
'------------------------------------------------------
objVSSProject.Checkout
'------------------------------------------------------
'--- Destroy object references.
'------------------------------------------------------
Set objVSSProject = Nothing
Set objSourceSafe = Nothing
End Sub