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.
For Each objCheck in objTable.Checks
strScripts(lngIndex) = objCheck.Script
lngIndex = lngIndex + 1
objCheck.Remove
Next
Restore them back 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:
- Add a new temporary column
- Copy the existing content to the temp column
- Remove the old column
- Add a new column with the same name as the old one, but with the new data type
- Copy the contents of the temp column into this
- Remove the temp column
The code snippet below demonstrates this:
objDataBase.ExecuteImmediate "ALTER TABLE " & objTable.Name & _
" ADD " & objColumn.Name & "_ ntext"
objDataBase.ExecuteImmediate "UPDATE " & objTable.Name & _
" SET " & objColumn.Name & "_ = " & objColumn.Name
objDataBase.ExecuteImmediate "ALTER TABLE " & objTable.Name & _
" DROP COLUMN " & objColumn.Name
objDataBase.ExecuteImmediate "ALTER TABLE " & objTable.Name _
& " ADD " & objColumn.Name & " ntext"
objDataBase.ExecuteImmediate "UPDATE " & objTable.Name & " SET " _
& objColumn.Name & " = " & objColumn.Name & "_"
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
- 1st June, 2006: Initial post