How to Change All Sql Columns of One DataType into Another

Question

I have a database (Sql Server 2005) where there are dozens of tables, each of which has a number of columns (on average 10-20) with datatype set to nvarchar(max). This is absolutely killing performance (some of these columns are being used for joins and some of the tables have 100K+ rows). I would like to change all of these columns to be varchar(250). What would be the best way to automate this? (I could use Management Studio, or I could create a utility to perform this through an ASP.net website that has access to the db, whichever is easier).

Answer 1

Here's a working script that uses INFORMATION_SCHEMA.COLUMNS to find all of the *varchar(max) columns and converts them to varchar(255):

declare @schema nvarchar(255)
declare @table nvarchar(255)
declare @col nvarchar(255)
declare @dtype nvarchar(255)
declare @sql nvarchar(max)

declare maxcols cursor for
select
    c.TABLE_SCHEMA,
    c.TABLE_NAME,
    c.COLUMN_NAME,
    c.DATA_TYPE
from
INFORMATION_SCHEMA.COLUMNS c
inner join INFORMATION_SCHEMA.TABLES t on
    c.TABLE_CATALOG = t.TABLE_CATALOG
    and c.TABLE_SCHEMA = t.TABLE_SCHEMA
    and c.TABLE_NAME = t.TABLE_NAME
    and t.TABLE_TYPE = 'BASE TABLE'
where
    c.DATA_TYPE like '%varchar'
    and c.CHARACTER_MAXIMUM_LENGTH = -1

open maxcols

fetch next from maxcols into @schema, @table, @col, @dtype

while @@FETCH_STATUS = 0
begin
    set @sql = 'alter table [' + @schema + '].[' + @table + 
        '] alter column [' + @col + '] ' + @dtype + '(255)'
    exec sp_executesql @sql

    fetch next from maxcols into @schema, @table, @col, @dtype
end

close maxcols
deallocate maxcols

This is about the only use of cursors that I ever condone, but it's a good one. Essentially, it finds all of the *varchar(max), builds the alter statement, and then executes it using sp_executesql.

Enjoy!

Answer 2

You can easily find them, using:

select 'alter table ' + quotename(o.name) + ' alter column ' + quotename(c.name) + ' varchar(250); '
from sys.columns c
  join
  sys.objects o
  on o.object_id = c.object_id
where o.type = 'U'
and c.user_type_id = 231
and c.max_length = -1

So now just grab the results of your query and run it.

Rob

Answer 3

I think the best way to do this task is to generate script for the current database and then replace nvarchar(max) with varchar(250) on the text file, and then create the new database. after that use the Import/export utilities to transfer the data to the new database.