Allen White

Programmatically migrating to the Unified Large Object Programming Model

Sorry about the long time since my last post. I've had a bunch of travel and project work preventing me from discovering new features of SMO to share. I hope I'm back on track now.

In any event, I'm attempting to take advantage of some of the features of SQL Server in our existing applications, and one of my favorite features is the Unified Large Object Programming Model. That's a fancy way of describing the (MAX) length value for varchar and varbinary data types.

So, to change datatypes in existing databases to use the MAX length types I built a small SMO program, so I don't have to browse all the tables to determine where the change is appropriate. I decided on an arbitrary value of (1000) - any datatype defined with a length of 1000 or greater will get converted to (MAX). You may want to do things differently, but this helps me show how easy it is to change the datatype using SMO.

After connecting to the server (you've seen that code in my blogs before) I connect to the database in question. I could have looped through the collection, but in this case I targeted a specific database. Once I determine that the database has a compatibility level of 9.0 (SQL Server 2005), I then grab the table collection and loop through that one table at a time. Then I grab the column collection and loop through the columns. Then, for each column I check the column's DataType.MaximumLength value. If it's greater than 999, regardless of data type, it qualifies for my change. By setting the DataType.MaximumLength value to -1 it effectively changes the column to the MAX length. I then set a flag indicating the table's been changed, and after evaluating all columns for the table, if the table has been changed I call the Alter method on the table.

Here's the code:

        Dim dbExpand As Database
        Dim tbcTables As TableCollection
        Dim tbTarget As Table

        ' Connect to the server

        dbExpand = srvMgmtServer.Databases("DemoDatabase")
        If dbExpand.CompatibilityLevel = CompatibilityLevel.Version90 Then      ' Database must be SQL 2005 level
            tbcTables = dbExpand.Tables
            For Each tbTarget In tbcTables
                Dim colcColumns As ColumnCollection
                Dim colTarget As Column
                Dim boolTableChgd As Boolean

                boolTableChgd = False

                colcColumns = tbTarget.Columns
                For Each colTarget In colcColumns
                    If colTarget.DataType.MaximumLength > 999 Then
                        colTarget.DataType.MaximumLength = -1
                        boolTableChgd = True
                    End If
                Next

                If boolTableChgd = True Then
                    tbTarget.Alter()
                End If
            Next
        End If

Once that's been done I no longer have to worry about applications that don't check the length of incoming data causing the loss of data.

Allen