Quickest way to alter datatypes

A database needs to be upgraded and as part of the upgrade, I want to convert all TEXT/NTEX开发者_C百科T fields in the dbo schema to the NVARCHAR(MAX) datatype. The data in those fields cannot be modified, though.

This upgrade needs to be done through an SQL Script!

So, how to do this?

(Without having to write ALTER TABLE bla ALTER COLUMN blabla NVARCHAR(MAX) blablabla...)

In the assumption you only use "system types" in your tables you can look up the columns with the types you want to change and call the updates in a cursor.

declare @nsql nvarchar(2000)
  , @table sysname
  , @column sysname
  , @type sysname
  , @is_nullable bit

declare cur cursor local read_only
for 
select tableName = OBJECT_NAME([object_id]), columnName = name, type = TYPE_NAME(system_type_id), is_nullable from sys.columns where TYPE_NAME(system_type_id) in ('text', 'ntext')

open cur
fetch next from cur into @table, @column, @type, @is_nullable

WHILE (@@FETCH_STATUS = 0)
  BEGIN
    select @nsql = N'ALTER TABLE ' + quotename(@table) + N' ALTER COLUMN ' + quotename(@column) + N' '
    if @type = N'text'
      select @nsql = @nsql + N' VARCHAR(MAX)'
    if @type = N'ntext'
      select @nsql = @nsql + N' NVARCHAR(MAX)'
    if @is_nullable = 0
      select @nsql = @nsql + N' NOT NULL'
    exec(@nsql)

    fetch next from cur into @table, @column, @type, @is_nullable
  END

close cur
deallocate cur

If you also use "alias types" you will have to go through a bit more pain to fix them.