How to Change All Sql Columns of One DataType into Another

performance, sql, sql-server, sql-server-2005, types

Solution

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!

Problem

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).

Original source