How to use SMO to create "Table Change" scripts from "Create Scripts"

Question

I recently needed to run Create Tables AND Update Tables scripts using SMO on my application's MSSQL back-end and found out (not surprisingly) that you cannot simply replace the CREATE TABLE statement with an ALTER TABLE as you can with Stored Procedures...

So I went about finding out how SSMS does it by changing a table and simply selecting "Generate Change Script" on the table. I then used this change script to determine what my function requires in order to make this more generalised and using the entire list of tables in my CREATE TABLE script file for a new installation.

For anyone interested, below under Answers is my Function (In VB.NET) using SMO. Its in no means perfect, but I hope you will find it a good starting point if not solution to the same problem.

Answer 1

My example assumes the following:

• That you have already connected to a server and local database using SMO and assigned the local db to the "dbLocal" variable.
• That your project contains a SQL Script file with CREATE TABLE statements (Passed to the function as a string parameter)

(I have included my GetFileContents Function too just in case)

Public Function UpdateCreateTables(ByVal SQLFilePath As String) As Boolean
    'Open CREATE TABLE Script file
    Dim strFilePath As String = SQLFilePath
    Dim strErr As String = ""
    Dim encEncoding As System.Text.Encoding = Nothing
    Dim strContents As String = GetFileContents(strFilePath, encEncoding, strErr)
    If strErr = "" Then
        'Successfully Read

        'Prepare
        Dim SQLSCRIPT As String = "" & _
            "BEGIN TRANSACTION" & _
                "SET QUOTED_IDENTIFIER ON" & _
                "SET ARITHABORT ON" & _
                "SET NUMERIC_ROUNDABORT OFF" & _
                "SET CONCAT_NULL_YIELDS_NULL ON" & _
                "SET ANSI_NULLS ON" & _
                "SET ANSI_PADDING ON" & _
                "SET ANSI_WARNINGS ON" & _
            "COMMIT"

        'Run Transaction Script
        dbLocal.ExecuteNonQuery(SQLSCRIPT, Microsoft.SqlServer.Management.Common.ExecutionTypes.ContinueOnError)

        'Inject All Table Names with a "TMP_" prefix
        strContents = strContents.Replace("CREATE TABLE [dbo].[", "CREATE TABLE [dbo].[TMP_")
        'Inject All Constraints with a TMP_ Prefix
        strContents = strContents.Replace("CONSTRAINT [PK_", "CONSTRAINT [PK_TMP_")

        SQLSCRIPT = strContents

        'Run Script (Create TMP tables)
        dbLocal.ExecuteNonQuery(SQLSCRIPT, Microsoft.SqlServer.Management.Common.ExecutionTypes.ContinueOnError)

        'Loop through Each TMP table and copy original table data across
        For Each dbTable As Table In dbLocal.Tables()
            If dbTable.Name.Contains("TMP_") Then
                Dim strTable_TempName As String = dbTable.Name
                Dim strTable_OrigName As String = Strings.Replace(dbTable.Name, "TMP_", "")

                Dim dbTempTable As Table = dbLocal.Tables(strTable_TempName)
                Dim dbOrigTable As Table = dbLocal.Tables(strTable_OrigName)

                'Get TmpTable Column Names
                Dim strTempTable_Columns As String = ""
                Dim strOrigTable_Columns As String = ""
                For Each col As Column In dbTempTable.Columns
                    strTempTable_Columns = strTempTable_Columns & col.Name & ", "
                    'Find Matching Column in Orig Table
                    If dbOrigTable.Columns.Contains(col.Name) Then
                        strOrigTable_Columns = strOrigTable_Columns & col.Name & ", "
                    Else
                        strOrigTable_Columns = strOrigTable_Columns & "NULL" & ", "
                    End If
                Next
                strTempTable_Columns = Strings.Left(strTempTable_Columns, strTempTable_Columns.Length - 2) 'Remove trailing comma+space
                strOrigTable_Columns = Strings.Left(strOrigTable_Columns, strOrigTable_Columns.Length - 2) 'Remove trailing comma+space

                'alter LOCK_ESCALATION
                dbTempTable.LockEscalation = LockEscalationType.Table

                'Get Primary Key
                Dim PK_Name As String = ""
                For Each oIndex As Index In dbTempTable.Indexes
                    If oIndex.IndexKeyType = IndexKeyType.DriPrimaryKey Then
                        ' Primary key found
                        PK_Name = oIndex.Name
                    End If
                Next

                SQLSCRIPT = "" & _
                    "BEGIN TRANSACTION" & vbCrLf & _
                        "GO" & vbCrLf & _
                        "SET IDENTITY_INSERT dbo." & strTable_TempName & " ON" & vbCrLf & _
                        "GO" & vbCrLf & _
                            "IF EXISTS(SELECT * FROM dbo." & strTable_OrigName & ")" & vbCrLf & _
                            "   EXEC('INSERT INTO dbo." & strTable_TempName & " (" & strTempTable_Columns & ")" & vbCrLf & _
                            "       SELECT " & strOrigTable_Columns & " FROM dbo." & strTable_OrigName & " WITH (HOLDLOCK TABLOCKX)')" & vbCrLf & _
                            "   GO" & vbCrLf & _
                        "SET IDENTITY_INSERT dbo." & strTable_TempName & " OFF" & vbCrLf & _
                        "GO" & vbCrLf & _
                        "DROP TABLE dbo." & strTable_OrigName & "" & vbCrLf & _
                        "GO" & vbCrLf & _
                        "EXECUTE sp_rename N'dbo." & PK_Name & "', '" & Replace(PK_Name, "TMP_", "") & "', 'OBJECT'" & vbCrLf & _
                        "GO" & vbCrLf & _
                        "EXECUTE sp_rename N'dbo." & strTable_TempName & "', N'" & strTable_OrigName & "', 'OBJECT'" & vbCrLf & _
                        "GO" & vbCrLf & _
                    "COMMIT"

                'Run Transaction Script
                dbLocal.ExecuteNonQuery(SQLSCRIPT, Microsoft.SqlServer.Management.Common.ExecutionTypes.ContinueOnError)

            End If
        Next

        UpdateCreateTables = True
    Else
        UpdateCreateTables = False
    End If

End Function
Public Function GetFileContents(ByVal FullPath As String, ByRef encEncoding As System.Text.Encoding, Optional ByRef ErrInfo As String = "") As String
    Dim strContents As String
    Dim objReader As StreamReader

    Try
        objReader = New StreamReader(FullPath, True)
        encEncoding = objReader.CurrentEncoding
        strContents = objReader.ReadToEnd()
        objReader.Close()
        Return strContents
    Catch Ex As Exception
        ErrInfo = Ex.Message
        Return Nothing
    End Try
End Function