SQL Server: How do I convert all varchar(max) columns to text type?

Question

How do I convert all varchar(max) columns to text type? I know varchar(max) is recommended but I still want to use type text because CakePHP doesn't automagically convert varchar(max) to textarea (HTML form). How do I do it? Thanks in advance

Answer 1

Executing the following will give you a list of queries you can run to achieve this:

SELECT 
  'ALTER TABLE [' + [TABLE_NAME] + '] ALTER COLUMN [' + [COLUMN_NAME] + '] TEXT '
   + CASE WHEN [IS_NULLABLE] = 'NO' THEN 'NOT NULL' ELSE 'NULL' END
FROM 
  INFORMATION_SCHEMA.COLUMNS 
WHERE 
  DATA_TYPE = 'VARCHAR' AND CHARACTER_MAXIMUM_LENGTH = -1

Make sure to backup your database before running the queries generated by this query because I'm not 100% certain if there would be any ill side-effects... but I imagine this would work just fine for you.