Check in New DDL to SourceSafe

'===============================================================================
'- generate scripts to rebuild all objects in [SQL server name]/[SQL database name]
'- check scripts into SourceSafe whenever current script is different than VSS's
'
'Revisions: When, Who, Why
' 03/20/2001Rich Cowley (cowley_rich@hotmail.com)Original Version
'===============================================================================

'General Constants
Const SCRIPT_DIRECTORY       = "c:\WorkingDirectory\CheckInNewSQLDDL" 'working directory on local machine

'SourceSafe Constants
Const VSS_ROOTPROJECT_NAME   = "$/"
Const VSS_SUBPROJECT_NAME    = "DATABASE/[SQL server name]_[SQL database name]/"
Const VSS_INI_PATH           = "\\[SourceSafe server name]\[SourceSafe path]\srcsafe.ini"
Const VSS_USERNAME           = "[SourceSafe administrator name]"
Const VSS_PASSWORD           = "[SourceSafe administrator password]"

'SQL DMO Constants
Const SQL_SERVERNAME         = "[SQL server name]"
Const SQL_USERNAME           = "[SQL administrator name]"
Const SQL_PASSWORD           = "[SQL administrator password]"
Const SQL_DBNAME             = "[SQL database name]"
Const SQLDMOScript_Default = 4
Const SQLDMOScript_Drops = 1
Const SQLDMOScript_Triggers = 16
Const SQLDMOScript_Indexes = 73736

'FileSystemObject constants
Const ForReading = 1
Const ForWriting = 2

Dim oSQLServer      'As SQLDMO.SQLServer2
Dim oDatabase       'As SQLDMO.Database2
Dim oDatabaseObject
Dim sScript         'As String
Dim oFSO            'As Scripting.FileSystemObject
Dim oFolder         'As Folder
Dim oFile           'As File
Dim oTS             'As Scripting.TextStream
Dim sCurrDirectory  'As String
Dim sFileName       'As String
Dim i               'As Integer
Dim iScriptOptions  'As Integer
Dim sObjectType     'As String

Dim oVSSDatabase    'As SourceSafeTypeLib.VSSDatabase
Dim oVSSItem        'As VSSItem
Dim sVSSItemPath    'As String
Dim sVSSLabel       'As String 'label used when checking items back in
Dim sCheckedScript  'As String
    
Dim sTempScript1    'As String
Dim sTempScript2    'As String
    
    '=======================
    'Script every object in the database (tables, views, SPs, etc.)
    'If current script is different than SourceSafe's, check in new version.
    'Add new SourceSafe item if current script was not on SourceSafe to begin with.
    '=======================
    
    'set up SourceSafe environment
    Set oVSSDatabase = wScript.CreateObject("SourceSafe")
    oVSSDatabase.Open VSS_INI_PATH, VSS_USERNAME, VSS_PASSWORD
    
    'establish SQL Server and FSO environments
    Set oSQLServer = wScript.CreateObject("SQLDMO.SQLServer2")
    oSQLServer.Connect SQL_SERVERNAME, SQL_USERNAME, SQL_PASSWORD
    Set oDatabase = oSQLServer.Databases(SQL_DBNAME)
    Set oFSO = wScript.CreateObject("Scripting.FileSystemObject")

    '--------    
    'tables
    '--------
    sObjectType = "Tables"
    sVSSItemPath = VSS_ROOTPROJECT_NAME & VSS_SUBPROJECT_NAME & sObjectType & "/"
    Set oVSSItem = oVSSDatabase.VSSItem(sVSSItemPath)
    'set up clean working directory
    sCurrDirectory = SCRIPT_DIRECTORY & "\" & sObjectType
    On Error Resume Next
    If oFSO.FolderExists(sCurrDirectory) Then
        oFSO.DeleteFolder sCurrDirectory, True
    End If
    Set oFolder = oFSO.CreateFolder(sCurrDirectory)
    'cycle through the objects
    For Each oDatabaseObject In oDatabase.Tables
        If oDatabaseObject.SystemObject Then
            'do nothing (bypass system objects)
        Else
            iScriptOptions = SQLDMOScript_Default + SQLDMOScript_Drops + SQLDMOScript_Triggers + SQLDMOScript_Indexes
            sScript = oDatabaseObject.Script(iScriptOptions)
            sVSSItemPath = VSS_ROOTPROJECT_NAME & VSS_SUBPROJECT_NAME & sObjectType & "/" & oDatabaseObject.Name & ".sql"
            sFileName = sCurrDirectory & "\" & oDatabaseObject.Name & ".sql"
            On Error Resume Next
            Set oVSSItem = oVSSDatabase.VSSItem(sVSSItemPath)
            If Err = 0 Then 'item is already on SourceSafe
                oVSSItem.Checkout "Checked out by automated process", sFileName
                Set oTS = oFSO.OpenTextFile(sFileName, ForReading)
                sCheckedScript = oTS.Read(1000000)
                oTS.Close
                'scrub the two scripts (remove any trailing CrLfs) and compare them
                sTempScript1 = Trim(Replace(sCheckedScript, Chr(13) & Chr(10), " "))
                sTempScript2 = Trim(Replace(sScript, Chr(13) & Chr(10), " "))
                If sTempScript1 = sTempScript2 Then
                   'new script is identical to its last check-in version
                    oVSSItem.UndoCheckout
                Else 'script has changed since its last check-in
                    'overlay file with new (unscrubbed) script and check back in
                    Set oTS = oFSO.OpenTextFile(sFileName, ForWriting, True)
                    oTS.WriteLine (sScript)
                    oTS.Close
                    oVSSItem.Checkin "Schema Altered", sFileName
                End If
            Else 'item does not yet exist on SourceSafe; add it
                Set oTS = oFSO.OpenTextFile(sFileName, ForWriting, True)
                oTS.Write (sScript)
                oTS.Close
                Set oVSSItem = oVSSDatabase.VSSItem(VSS_ROOTPROJECT_NAME & VSS_SUBPROJECT_NAME & sObjectType & "/")
                oVSSItem.Add sFileName
            End If
        End If
    Next

    '--------
    'views
    '--------
    sObjectType = "Views"
    sVSSItemPath = VSS_ROOTPROJECT_NAME & VSS_SUBPROJECT_NAME & sObjectType & "/"
    Set oVSSItem = oVSSDatabase.VSSItem(sVSSItemPath)
    'set up clean working directory
    sCurrDirectory = SCRIPT_DIRECTORY & "\" & sObjectType
    If oFSO.FolderExists(sCurrDirectory) Then
        oFSO.DeleteFolder sCurrDirectory, True
    End If
    Set oFolder = oFSO.CreateFolder(sCurrDirectory)
    'cycle through the objects
    For Each oDatabaseObject In oDatabase.Views
        If oDatabaseObject.SystemObject Then
            'do nothing (bypass system objects)
        Else
            iScriptOptions = SQLDMOScript_Default + SQLDMOScript_Drops + SQLDMOScript_Triggers + SQLDMOScript_Indexes 
            sScript = oDatabaseObject.Script(iScriptOptions)
            sVSSItemPath = VSS_ROOTPROJECT_NAME & VSS_SUBPROJECT_NAME & sObjectType & "/" & oDatabaseObject.Name & ".sql"
            sFileName = sCurrDirectory & "\" & oDatabaseObject.Name & ".sql"
            On Error Resume Next
            Set oVSSItem = oVSSDatabase.VSSItem(sVSSItemPath)
            If Err = 0 Then 'item is already on SourceSafe
                oVSSItem.Checkout "Checked out by automated process", sFileName
                Set oTS = oFSO.OpenTextFile(sFileName, ForReading)
                sCheckedScript = oTS.Read(1000000)
                oTS.Close
                'scrub the two scripts (remove any trailing CrLfs) and compare them
                sTempScript1 = Trim(Replace(sCheckedScript, Chr(13) & Chr(10), " "))
                sTempScript2 = Trim(Replace(sScript, Chr(13) & Chr(10), " "))
                If sTempScript1 = sTempScript2 Then
                   'new script is identical to its last check-in version
                    oVSSItem.UndoCheckout
                Else 'script has changed since its last check-in
                    'overlay file with new (unscrubbed) script and check back in
                    Set oTS = oFSO.OpenTextFile(sFileName, ForWriting, True)
                    oTS.WriteLine (sScript)
                    oTS.Close
                    oVSSItem.Checkin "Schema Altered", sFileName
                End If
            Else 'item does not yet exist on SourceSafe; add it
                Set oTS = oFSO.OpenTextFile(sFileName, ForWriting, True)
                oTS.Write (sScript)
                oTS.Close
                Set oVSSItem = oVSSDatabase.VSSItem(VSS_ROOTPROJECT_NAME & VSS_SUBPROJECT_NAME & sObjectType & "/")
                oVSSItem.Add sFileName
            End If
        End If
    Next

    '--------
    'stored procedures
    '--------
    sObjectType = "StoredProcedures"
    sVSSItemPath = VSS_ROOTPROJECT_NAME & VSS_SUBPROJECT_NAME & sObjectType & "/"
    Set oVSSItem = oVSSDatabase.VSSItem(sVSSItemPath)
    'set up clean working directory
    sCurrDirectory = SCRIPT_DIRECTORY & "\" & sObjectType
    If oFSO.FolderExists(sCurrDirectory) Then
        oFSO.DeleteFolder sCurrDirectory, True
    End If
    Set oFolder = oFSO.CreateFolder(sCurrDirectory)
    'cycle through the objects
    For Each oDatabaseObject In oDatabase.StoredProcedures
        If oDatabaseObject.SystemObject Then
            'do nothing (bypass system objects)
        Else
            iScriptOptions = SQLDMOScript_Default + SQLDMOScript_Drops
            sScript = oDatabaseObject.Script(iScriptOptions)
            sVSSItemPath = VSS_ROOTPROJECT_NAME & VSS_SUBPROJECT_NAME & sObjectType & "/" & oDatabaseObject.Name & ".sql"
            sFileName = sCurrDirectory & "\" & oDatabaseObject.Name & ".sql"
            On Error Resume Next
            Set oVSSItem = oVSSDatabase.VSSItem(sVSSItemPath)
            If Err = 0 Then 'item is already on SourceSafe
                oVSSItem.Checkout "Checked out by automated process", sFileName
                Set oTS = oFSO.OpenTextFile(sFileName, ForReading)
                sCheckedScript = oTS.Read(1000000)
                oTS.Close
                'scrub the two scripts (remove any trailing CrLfs) and compare them
                sTempScript1 = Trim(Replace(sCheckedScript, Chr(13) & Chr(10), " "))
                sTempScript2 = Trim(Replace(sScript, Chr(13) & Chr(10), " "))
                If sTempScript1 = sTempScript2 Then
                   'new script is identical to its last check-in version
                    oVSSItem.UndoCheckout
                Else 'script has changed since its last check-in
                    'overlay file with new (unscrubbed) script and check back in
                    Set oTS = oFSO.OpenTextFile(sFileName, ForWriting, True)
                    oTS.WriteLine (sScript)
                    oTS.Close
                    oVSSItem.Checkin "Schema Altered", sFileName
                End If
            Else 'item does not yet exist on SourceSafe; add it
                Set oTS = oFSO.OpenTextFile(sFileName, ForWriting, True)
                oTS.Write (sScript)
                oTS.Close
                Set oVSSItem = oVSSDatabase.VSSItem(VSS_ROOTPROJECT_NAME & VSS_SUBPROJECT_NAME & sObjectType & "/")
                oVSSItem.Add sFileName
            End If
        End If
    Next

    '--------        
    'rules
    '--------
    sObjectType = "Rules"
    sVSSItemPath = VSS_ROOTPROJECT_NAME & VSS_SUBPROJECT_NAME & sObjectType & "/"
    Set oVSSItem = oVSSDatabase.VSSItem(sVSSItemPath)
    'set up clean working directory
    sCurrDirectory = SCRIPT_DIRECTORY & "\" & sObjectType
    If oFSO.FolderExists(sCurrDirectory) Then
        oFSO.DeleteFolder sCurrDirectory, True
    End If
    Set oFolder = oFSO.CreateFolder(sCurrDirectory)
    'cycle through the objects
    For Each oDatabaseObject In oDatabase.Rules
        If oDatabaseObject.SystemObject Then
            'do nothing (bypass system objects)
        Else
            iScriptOptions = SQLDMOScript_Default + SQLDMOScript_Drops
            sScript = oDatabaseObject.Script(iScriptOptions)
            sVSSItemPath = VSS_ROOTPROJECT_NAME & VSS_SUBPROJECT_NAME & sObjectType & "/" & oDatabaseObject.Name & ".sql"
            sFileName = sCurrDirectory & "\" & oDatabaseObject.Name & ".sql"
            On Error Resume Next
            Set oVSSItem = oVSSDatabase.VSSItem(sVSSItemPath)
            If Err = 0 Then 'item is already on SourceSafe
                oVSSItem.Checkout "Checked out by automated process", sFileName
                Set oTS = oFSO.OpenTextFile(sFileName, ForReading)
                sCheckedScript = oTS.Read(1000000)
                oTS.Close
                'scrub the two scripts (remove any trailing CrLfs) and compare them
                sTempScript1 = Trim(Replace(sCheckedScript, Chr(13) & Chr(10), " "))
                sTempScript2 = Trim(Replace(sScript, Chr(13) & Chr(10), " "))
                If sTempScript1 = sTempScript2 Then
                   'new script is identical to its last check-in version
                    oVSSItem.UndoCheckout
                Else 'script has changed since its last check-in
                    'overlay file with new (unscrubbed) script and check back in
                    Set oTS = oFSO.OpenTextFile(sFileName, ForWriting, True)
                    oTS.WriteLine (sScript)
                    oTS.Close
                    oVSSItem.Checkin "Schema Altered", sFileName
                End If
            Else 'item does not yet exist on SourceSafe; add it
                Set oTS = oFSO.OpenTextFile(sFileName, ForWriting, True)
                oTS.Write (sScript)
                oTS.Close
                Set oVSSItem = oVSSDatabase.VSSItem(VSS_ROOTPROJECT_NAME & VSS_SUBPROJECT_NAME & sObjectType & "/")
                oVSSItem.Add sFileName
            End If
        End If
    Next

    '--------        
    'defaults
    '--------
    sObjectType = "Defaults"
    sVSSItemPath = VSS_ROOTPROJECT_NAME & VSS_SUBPROJECT_NAME & sObjectType & "/"
    Set oVSSItem = oVSSDatabase.VSSItem(sVSSItemPath)
    'set up clean working directory
    sCurrDirectory = SCRIPT_DIRECTORY & "\" & sObjectType
    If oFSO.FolderExists(sCurrDirectory) Then
        oFSO.DeleteFolder sCurrDirectory, True
    End If
    Set oFolder = oFSO.CreateFolder(sCurrDirectory)
    'cycle through the objects
    For Each oDatabaseObject In oDatabase.Defaults
        If oDatabaseObject.SystemObject Then
            'do nothing (bypass system objects)
        Else
            iScriptOptions = SQLDMOScript_Default + SQLDMOScript_Drops
            sScript = oDatabaseObject.Script(iScriptOptions)
            sVSSItemPath = VSS_ROOTPROJECT_NAME & VSS_SUBPROJECT_NAME & sObjectType & "/" & oDatabaseObject.Name & ".sql"
            sFileName = sCurrDirectory & "\" & oDatabaseObject.Name & ".sql"
            On Error Resume Next
            Set oVSSItem = oVSSDatabase.VSSItem(sVSSItemPath)
            If Err = 0 Then 'item is already on SourceSafe
                oVSSItem.Checkout "Checked out by automated process", sFileName
                Set oTS = oFSO.OpenTextFile(sFileName, ForReading)
                sCheckedScript = oTS.Read(1000000)
                oTS.Close
                'scrub the two scripts (remove any trailing CrLfs) and compare them
                sTempScript1 = Trim(Replace(sCheckedScript, Chr(13) & Chr(10), " "))
                sTempScript2 = Trim(Replace(sScript, Chr(13) & Chr(10), " "))
                If sTempScript1 = sTempScript2 Then
                   'new script is identical to its last check-in version
                    oVSSItem.UndoCheckout
                Else 'script has changed since its last check-in
                    'overlay file with new (unscrubbed) script and check back in
                    Set oTS = oFSO.OpenTextFile(sFileName, ForWriting, True)
                    oTS.WriteLine (sScript)
                    oTS.Close
                    oVSSItem.Checkin "Schema Altered", sFileName
                End If
            Else 'item does not yet exist on SourceSafe; add it
                Set oTS = oFSO.OpenTextFile(sFileName, ForWriting, True)
                oTS.Write (sScript)
                oTS.Close
                Set oVSSItem = oVSSDatabase.VSSItem(VSS_ROOTPROJECT_NAME & VSS_SUBPROJECT_NAME & sObjectType & "/")
                oVSSItem.Add sFileName
            End If
        End If
    Next
        
    '--------
    'user-defined data types
    '--------
    sObjectType = "UserDefinedDatatypes"
    sVSSItemPath = VSS_ROOTPROJECT_NAME & VSS_SUBPROJECT_NAME & sObjectType & "/"
    Set oVSSItem = oVSSDatabase.VSSItem(sVSSItemPath)
    'set up clean working directory
    sCurrDirectory = SCRIPT_DIRECTORY & "\" & sObjectType
    If oFSO.FolderExists(sCurrDirectory) Then
        oFSO.DeleteFolder sCurrDirectory, True
    End If
    Set oFolder = oFSO.CreateFolder(sCurrDirectory)
    'cycle through the objects
    For Each oDatabaseObject In oDatabase.UserDefinedDataTypes
        If oDatabaseObject.SystemObject Then
            'do nothing (bypass system objects)
        Else
            iScriptOptions = SQLDMOScript_Default + SQLDMOScript_Drops
            sScript = oDatabaseObject.Script(iScriptOptions)
            sVSSItemPath = VSS_ROOTPROJECT_NAME & VSS_SUBPROJECT_NAME & sObjectType & "/" & oDatabaseObject.Name & ".sql"
            sFileName = sCurrDirectory & "\" & oDatabaseObject.Name & ".sql"
            On Error Resume Next
            Set oVSSItem = oVSSDatabase.VSSItem(sVSSItemPath)
            If Err = 0 Then 'item is already on SourceSafe
                oVSSItem.Checkout "Checked out by automated process", sFileName
                Set oTS = oFSO.OpenTextFile(sFileName, ForReading)
                sCheckedScript = oTS.Read(1000000)
                oTS.Close
                'scrub the two scripts (remove any trailing CrLfs) and compare them
                sTempScript1 = Trim(Replace(sCheckedScript, Chr(13) & Chr(10), " "))
                sTempScript2 = Trim(Replace(sScript, Chr(13) & Chr(10), " "))
                If sTempScript1 = sTempScript2 Then
                   'new script is identical to its last check-in version
                    oVSSItem.UndoCheckout
                Else 'script has changed since its last check-in
                    'overlay file with new (unscrubbed) script and check back in
                    Set oTS = oFSO.OpenTextFile(sFileName, ForWriting, True)
                    oTS.WriteLine (sScript)
                    oTS.Close
                    oVSSItem.Checkin "Schema Altered", sFileName
                End If
            Else 'item does not yet exist on SourceSafe; add it
                Set oTS = oFSO.OpenTextFile(sFileName, ForWriting, True)
                oTS.Write (sScript)
                oTS.Close
                Set oVSSItem = oVSSDatabase.VSSItem(VSS_ROOTPROJECT_NAME & VSS_SUBPROJECT_NAME & sObjectType & "/")
                oVSSItem.Add sFileName
            End If
        End If
    Next

    '--------
    'user-defined functions
    '--------
    sObjectType = "UserDefinedFunctions"
    sVSSItemPath = VSS_ROOTPROJECT_NAME & VSS_SUBPROJECT_NAME & sObjectType & "/"
    Set oVSSItem = oVSSDatabase.VSSItem(sVSSItemPath)
    'set up clean working directory
    sCurrDirectory = SCRIPT_DIRECTORY & "\" & sObjectType
    If oFSO.FolderExists(sCurrDirectory) Then
        oFSO.DeleteFolder sCurrDirectory, True
    End If
    Set oFolder = oFSO.CreateFolder(sCurrDirectory)
    'cycle through the objects
    For Each oDatabaseObject In oDatabase.UserDefinedFunctions
        If oDatabaseObject.SystemObject Then
            'do nothing (bypass system objects)
        Else
            iScriptOptions = SQLDMOScript_Default + SQLDMOScript_Drops
            sScript = oDatabaseObject.Script(iScriptOptions)
            sVSSItemPath = VSS_ROOTPROJECT_NAME & VSS_SUBPROJECT_NAME & sObjectType & "/" & oDatabaseObject.Name & ".sql"
            sFileName = sCurrDirectory & "\" & oDatabaseObject.Name & ".sql"
            On Error Resume Next
            Set oVSSItem = oVSSDatabase.VSSItem(sVSSItemPath)
            If Err = 0 Then 'item is already on SourceSafe
                oVSSItem.Checkout "Checked out by automated process", sFileName
                Set oTS = oFSO.OpenTextFile(sFileName, ForReading)
                sCheckedScript = oTS.Read(1000000)
                oTS.Close
                'scrub the two scripts (remove any trailing CrLfs) and compare them
                sTempScript1 = Trim(Replace(sCheckedScript, Chr(13) & Chr(10), " "))
                sTempScript2 = Trim(Replace(sScript, Chr(13) & Chr(10), " "))
                If sTempScript1 = sTempScript2 Then
                   'new script is identical to its last check-in version
                    oVSSItem.UndoCheckout
                Else 'script has changed since its last check-in
                    'overlay file with new (unscrubbed) script and check back in
                    Set oTS = oFSO.OpenTextFile(sFileName, ForWriting, True)
                    oTS.WriteLine (sScript)
                    oTS.Close
                    oVSSItem.Checkin "Schema Altered", sFileName
                End If
            Else 'item does not yet exist on SourceSafe; add it
                Set oTS = oFSO.OpenTextFile(sFileName, ForWriting, True)
                oTS.Write (sScript)
                oTS.Close
                Set oVSSItem = oVSSDatabase.VSSItem(VSS_ROOTPROJECT_NAME & VSS_SUBPROJECT_NAME & sObjectType & "/")
                oVSSItem.Add sFileName
            End If
        End If
    Next

    '--------    
    'clean up
    '-------- 
    oSQLServer.Close
    Set oSQLServer = Nothing
    Set oDatabase = Nothing

    Set oVSSItem = Nothing
    Set oVSSDatabase = Nothing

    'toss the working folders
    oFSO.DeleteFolder SCRIPT_DIRECTORY & "\Tables", True
    oFSO.DeleteFolder SCRIPT_DIRECTORY & "\Views", True
    oFSO.DeleteFolder SCRIPT_DIRECTORY & "\StoredProcedures", True
    oFSO.DeleteFolder SCRIPT_DIRECTORY & "\Defaults", True
    oFSO.DeleteFolder SCRIPT_DIRECTORY & "\Rules", True
    oFSO.DeleteFolder SCRIPT_DIRECTORY & "\UserDefinedDataTypes", True
    oFSO.DeleteFolder SCRIPT_DIRECTORY & "\UserDefinedFunctions", True
    
    Set oFSO = Nothing
    Set oFolder = Nothing
    Set oFile = Nothing
    Set oTS = Nothing

    'process complete!
    MsgBox "SQL Objects successfully rolled to SourceSafe."