12,067,614 members (54,861 online)
This article describes the usage of SQL DMO to automate few aspects of DB maintenance.
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
text have to be changed to their Unicode equivalent (
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:
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:
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.