BLOGGEN HAR FLYTTAT TILL http://sql.underlandet.com

Tänk dig att du får följande uppgift:

“Vi behöver göra en liten ändring i den här databasen, som har hundratals tabeller och tusentals kolumner. Vi behöver ett bättre stöd för olika språk i applikationen så alla kolumner måste ändras till unicode (dvs varchar blir nvarchar, char blir nchar osv).”

Med små datamängder kan man alltid använda SQL Server Management Studio och göra ändringarna där. Men det är knappast ett alternativ när man har utvecklingsmiljöer, test-miljöer och produktionsmiljöer med flera installerade instanser av liknande databaser osv. När produktionssättningen av en sådan här ändring ska göras vill man inte behöva stänga sina system två veckor för att manuellt ändra kolumner.

En andra tanke skulle vara att just göra manuellt ändringar i en test-miljö och via Script-knappen i Management Studio få det SQL-script som används för att göra förändringen och spara ändringen till en text-fil. Men det skulle också vara extremt tidsödande. Jag skrev ovan om produktionsmiljöer med liknande databaser. Det är ett problem med ändra-manuellt-i-Management-Studio-och-spara-undan-scriptet-metoden. Är inte databaserna identiska måste samma procedur utföras för varje databas.

En tredje idé är ju förstås att scripta ut databaserna och göra ändringar med Sök/Ersätt i valfri text-editor. Så tänkte jag först när jag ställdes inför uppgiften. Men ganska snabbt slog jag idén ur hågen. I mitt fall har flera tabeller kolumnnamn som är datatyper (“Text” till exempel). Det gjorde enkel Sök/Ersätt i text ganska svårt. Dessutom skulle scriptet behöva omstruktureras en hel del för att saker och ting skulle göras i rätt ordning (droppa constraints och index, ändra tabellerna, återskapa constraints och index).

Därför testade jag mig fram med Microsoft.SqlServer.Management.Smo i VB.NET och kom fram till ganska lite kod som gör ett väldigt stort jobb. Jag älskar verkligen SMO! På första försöket blev jag 95% färdig. Två-tre försök senare hade jag en lösning som fungerade “universellt” för alla databaser.

Jag har även använt SMO och reguljära uttryck för att ändra alla stored procedures i en databas till att använda unicode-ekvivalenter, men den funktionen har så mycket buggar än så länge, så jag väntar lite med den. De två största problem jag haft med den är: Jag har inte ett korrekt reguljärt uttryck för att känna igen strängar. Det blir fel när det finns fyra eller sex enkla citattecken invid varandra i koden. Jag har inte heller fått till igenkänning av alla variabler och parametrar. Parametrarna är OK, det är rätt enkelt att matcha “@någonting<ett eller flera blanksteg>variabeltyp”. Men det är inte alls lika enkelt att hitta alla förekomster av varchar, text eller char i löpande SQL-kod (exempelvis “SELECT CAST(kolumnen AS varchar(10)) FROM tabellen” osv. Men jag kanske finslipar lite, och återkommer när den är “fulländad” (as if..). Tills vidare körde jag min funktion och blev sedan sittande i nästan två dagar med rättning av koden. Men det sparade ändå massor av tid, jämfört med att helt manuellt göra förändringarna.

Här är VB.NET-koden för att ändra tabeller till unicode. En klass, med en publik och en privat metod:

Imports Microsoft.SqlServer.Management
Public Class TableUnicodyfier 
    'Sex globaler, två för att lagra fel och constraints, två som är namn på SMO-objekt 
    'och två som är självaste SMO-objekten. Inga publika variabler. 
    Private clConstraints As Collection 
    Private clErrors As Collection 
    Private s As Smo.Server 
    Private d As Smo.Database 
    Private sDBName As String 
    Private sInstanceName As String 
    Public Sub New(ByVal DBName As String, ByVal InstanceName As String) 
        sDBName = DBName 
        sInstanceName = InstanceName 
    End Sub 
    'Publik metod som returnerar ett SQL-script för att ändra alla text-kolumner till Unicode. 
    Public Function ReplaceAllTablesWithUnicode() As String 

        'Variabler 
        Dim sError As String = "" 
        Dim j As Integer = 0 
        Dim i As Integer = 0 
        Dim str As String = "" 

        'Initiera object 
        clConstraints = New Collection() 
        clErrors = New Collection() 
        s = New Smo.Server(sInstanceName) 
        d = s.Databases(sDBName)

        'Viktigt!! Jag föredrar att kunna granska alla ändringar i text innan de genomförs (jag kanske är feg?). 
        'Om inte raden nedan finns med görs alla ändringar "på riktigt". Finns den med sparas bara alla SQL-anrop 
        'och kan hämtas sedan. 
        s.ConnectionContext.SqlExecutionModes = Common.SqlExecutionModes.CaptureSql 
 

        'Loopa igenom alla tabeller och kör replaceTableWithUnicode 
        For Each t As Smo.Table In d.Tables 
            If Not t.IsSystemObject Then 
                replaceTableWithUnicode(t.Name, True) 
            End If 
            i += 1 
            Application.DoEvents() 
        Next 
 

        'Eftersom replaceTableWithUnicode bara droppar Foreign Keys men inte återskapar dem så gör vi det sist av allt. 
        'Anledningen är förstås att alla tabeller måste ändras innan vi kan återskapa FK:n igen. Annars riskerar vi 
        'att försöka skapa en FK mellan en varchar-kolumn i tabell A och en nvarchar-kolumn i tabell B. 
        'Därför loopar vi igenom clConstraints och kör Create på varje FK. 
        For Each f2 As Object In clConstraints 
            f2.create() 
        Next 
 

        'Loopa igenom all SQL-text som SMO sparat åt oss istället för att genomföra förändringarna på riktigt. 
        'Bygg upp en resultat-sträng 
        For Each str2 As String In s.ConnectionContext.CapturedSql.Text 
            str &= str2 & vbCrLf & "GO" & vbCrLf 
        Next 
 

        'Om några fel uppstod under körningen så har de objekten lagrats i clErrors. Loopa igenom den och 
        'lägg till även den SQL-koden sist i resultat-strängen. 
        If clErrors.Count > 0 Then 
            str &= "--Errors occured while saving scripts" & vbCrLf 
            For Each o As Object In clErrors 
                For Each strScript As String In o.script() 
                    str &= strScript & vbCrLf 
                Next 
            Next 
        End If 

        'str innehåller nu: 
        '- Drop statements för alla Foreign Key constraints 
        '- Drop-statements för alla index, primärnycklar, defaults och triggers 
        '- Alter statements för alla tabeller 
        '- Create-statements för alla index, primärnycklar, defaults och  triggers 
        '- Create-statements för alla Foreign Key constraints 
        Return str 
    End Function

    Private Function replaceTableWithUnicode(ByVal tableName As String, ByVal commit As Boolean) As String 
        Dim cl As New Collection 
        Dim bChange As Boolean 
        Dim t As Smo.Table = d.Tables(tableName) 
        Dim c As Smo.Column 
        Dim str As String = "" 


 
        'Loopa igenom alla kolumner. Ändra dem till respektive Unicode-ekvivalent 
        For Each c In t.Columns 
            bChange = False 
            Select Case c.DataType.Name 
                Case "varchar" 
                    bChange = True 
                    If c.DataType.MaximumLength > 4000 Then 
                        c.DataType = Smo.DataType.NVarCharMax 
                    Else 
                        c.DataType = Smo.DataType.NVarChar(c.DataType.MaximumLength) 
                    End If 
                Case "char" 
                    bChange = True 
                    c.DataType = Smo.DataType.NChar(c.DataType.MaximumLength) 
                Case "text" 
                    bChange = True 
                    c.DataType = Smo.DataType.NVarCharMax 
            End Select 
            'Notera att vi inte har gjort Alter på tabellen ännu. Än så länge har vi bara ändrat i kolumn-objekten. 
            'Om någon kolumn ska ändras så är det säkrast att återskapa alla default-constraints, annars kommer 
            'SQL Server att gnälla. Vi loopar igenom dem, lägger till dem i cl och droppar dem sedan. På så sätt 
            'genererar ett DROP-statement till vår SQL-kod, och vi kan göra Create senare när tabellen är ändrad. 
            If bChange Then 
                If Not c.DefaultConstraint Is Nothing Then 
                    cl.Add(c.DefaultConstraint) 
                    c.DefaultConstraint.Drop() 
                End If 
            End If 
        Next 

         Try 
            'Nu ska vi hitta alla FK-constraints som refererar till den här tabellen. 
            'För varje FK som refererar till den här tabellen - lägg till den i både funktionens interna 
            'cl2 och i klassvariablen clConstraints 
            'Sist - loopa igenom alla constraints i cl2 och droppa. Resultatet blir att alla FK kommer att droppas 
            'men själva objekten kommer att finnas kvar i clConstraints, för att anropande funktion ska kunna återskapa dem 
            For Each t2 As Smo.Table In d.Tables 
                Dim cl2 As New Collection 
                For Each f2 As Smo.ForeignKey In t2.ForeignKeys 
                    If f2.ReferencedTable.ToUpper() = tableName.ToUpper() Then 
                        cl2.Add(f2) 
                        clConstraints.Add(f2) 
                    End If 
                Next 
                For Each f2 As Smo.ForeignKey In cl2 
                    f2.Drop() 
                Next 
            Next 
            'Droppa även alla FK som är skapade i aktuell tabell och som refererar andra tabeller 
            Do Until t.ForeignKeys.Count = 0 
                clConstraints.Add(t.ForeignKeys(0)) 
                t.ForeignKeys(0).Drop() 
            Loop 
            'Droppa alla index. Lägg först till indexet i den privata variabeln cl 
            Do Until t.Indexes.Count = 0 
                'Om inte den här raden är med funkar inte koden för Primärnycklar. Raden gör ingenting mer än att 
                'läsa indextypen, men tydligen görs något i själva Index-objektet som initierar den korrekt för primärnycklar. 
                Dim it As Smo.IndexKeyType = t.Indexes(0).IndexKeyType 
                cl.Add(t.Indexes(0)) 
                t.Indexes(0).Drop() 
            Loop
            'Och slutligen samma sak med alla triggers i tabellen 
            Do Until t.Triggers.Count = 0 
                cl.Add(t.Triggers(0)) 
                t.Triggers(0).Drop() 
            Loop 

            'Sådärja, då är alla beroende objekt droppade. Då gör vi Alter på tabellen för att ge genomslag 
            'åt våra förändringar av kolumn-datatyper 
            Try 
                t.Alter() 
            Catch ex As Exception 
                'Och det här kan ju gå fel om vi glömt något, så då sparar vi i sådant fall undan tabellen i clErrors 
                MsgBox("Error when altering table " & t.Name) 
                clErrors.Add(t) 
            End Try

            'Nu är tabellen ändrad, men den har varken index, primärnycklar defaults eller triggers. 
            'Tur att vi sparat undan de objekten i privata variabeln cl. Loopa och återskapa. 
            For Each o As Object In cl 
                Try 
                    o.Create() 
                Catch ex As Exception 
                    'Det här är fult, men jag vill direkt få reda på när något går fel, och få se en Stacktrace. 
                    'I normala fall ska naturligtvis inte en sådan här klass försöka sig på att kommunicera interaktivt. 
                    'Den som orkar fixar självklart en ErrorMessages-collection som public property i klassen. 
                    MsgBox("Error when creating object(s)" & vbCrLf & ex.ToString()) 
                    clErrors.Add(o) 
                End Try 
            Next 
        Catch ex As Exception 
            MsgBox(ex.ToString()) 
        End Try 
        Return str 
    End Function
End Class

Andra intressanta blogginlägg om: Unicode, SQL Server, Microsoft, Microsoft.SqlServer.Management.Smo