How to change all Sql columns of one data type to another

Question

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

Answer 1

Here's a script that uses INFORMATION_SCHEMA.COLUMNS to find all *varchar(max) columns and convert 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 applies to the only use of cursors I've ever indulged, but it's good. Essentially, it finds everything *varchar(max) , builds the alter statement, and then executes it with sp_executesql .

Enjoy it!

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 accomplish this task is to create a script for the current database, and then replace nvarchar (max) with varchar (250) in a text file, and then create a new database. then use the import / export utilities to transfer data to the new database.