Using SQL DMO to Alter the Data Type of Table Columns

• Download source files - 3.3 KB

Introduction

This article describes the usage of SQL DMO to automate few aspects of DB maintenance.

Background

There may be instances during the development life cycle, where you may come across the need to change the data type of some table columns. One such instance that I have come across is while enabling multi-lingual support to your application. In that situation, the columns with data types like char, varchar and text have to be changed to their Unicode equivalent (nchar, nvarchar and ntext respectively).

Points of Interest

If the database has many tables, and each of them has many columns whose data type needs to be altered, the job becomes tedious. One way out is to automate this task. This article tries to address it to some extent. The examples given in the article work on a SQL Server 2000 database only.

Few of the challenges faced during such tasks are:

• Altering columns that have defaults / checks associated with them
• Altering columns of type "text"
• Changing the stored procedures and triggers that use these columns

Using the Code

Columns that have defaults and checks associated with them cannot be altered directly. The defaults and checks have to be dropped before you can alter such columns.

'Dropping the checks and save the corresponding script so that it can be enabled later
For Each objCheck in objTable.Checks
    strScripts(lngIndex) = objCheck.Script
    lngIndex = lngIndex + 1
    objCheck.Remove
Next

Restore them back after the column is altered.

'Restore all the checks after the column is altered
For lngIndex = 0 To lngCount - 1
    objDataBase.ExecuteImmediate strScripts(lngIndex)
Next

Columns of the text type cannot be altered directly either using the T-SQL alter statement or SQL DMO's alter method on the column object. One of the approaches to work around it is to introduce a new temporary column.

The technique is simple, and it involves the following steps:

1. Add a new temporary column
2. Copy the existing content to the temp column
3. Remove the old column
4. Add a new column with the same name as the old one, but with the new data type
5. Copy the contents of the temp column into this
6. Remove the temp column

The code snippet below demonstrates this:

'Add a new temporary column
objDataBase.ExecuteImmediate "ALTER TABLE " & objTable.Name & _
			" ADD " & objColumn.Name & "_ ntext"

'Copy the existing content to the temp column
objDataBase.ExecuteImmediate "UPDATE " & objTable.Name & _
			" SET " & objColumn.Name & "_ = " & objColumn.Name

'Remove the old column
objDataBase.ExecuteImmediate "ALTER TABLE " & objTable.Name & _
			" DROP COLUMN " & objColumn.Name

'Add a new column with the same name as the old one, but with the new data type
objDataBase.ExecuteImmediate "ALTER TABLE " & objTable.Name _
			& " ADD " & objColumn.Name & " ntext"

'Copy the contents of the temp column into this
objDataBase.ExecuteImmediate "UPDATE " & objTable.Name & " SET " _
			& objColumn.Name & " = " & objColumn.Name & "_"

'Remove the temp column
objDataBase.ExecuteImmediate "ALTER TABLE " & objTable.Name & _
			" DROP COLUMN " & objColumn.Name & "_"

Changing the column data type may in turn affect other areas, like stored procedures / triggers that use these columns.

SQL DMO allows the DB objects to be scripted to a file or a string. A find and replace can be performed on this string and then run it to update the DB objects back.

History

• 1^st June, 2006: Initial post