Change column data type across database

Question

I have a bunch of tables with many columns of data type ntext. I wish to change all such columns in all tables of a particular database to nvarchar(max), because of planned deprecation (EDIT: also because I can't use such columns with DISTINCT, GROUP BY etc.). Is it possible to accomplish this without using ALTER TABLE with ALTER COLUMN for each table manually?

I am using MS SQL Server 2008.

Answer 1

No, there's no magic or hand-waving here. It'd be great if synonyms, say, applied to types, but that is not the case. If you want to make these columns first-class citizens, you'll need to change the table. You can automate this to some degree, though I won't post code to help with this unless you specify what you mean exactly by "manually" and why you think this will be a significant burden you want to avoid.

To automate this, you could say:

DECLARE @sql NVARCHAR(MAX) = N'';

SELECT @sql += CHAR(13) + CHAR(10) + N'ALTER TABLE '
  + QUOTENAME(OBJECT_SCHEMA_NAME([object_id]))
  + '.' + QUOTENAME(OBJECT_NAME([object_id])) 
  + ' ALTER COLUMN ' + QUOTENAME(name) + ' NVARCHAR(MAX)'
  + CASE is_nullable WHEN 0 THEN ' NOT NULL;' ELSE ';' END
FROM sys.columns
WHERE system_type_id = 99;

PRINT @sql;

-- EXEC sp_executesql @sql;

That said, you probably don't want to use these columns with distinct / group by anyway.

Answer 2

No, if you want to alter the column type you're going to have to use an ALTER COLUMN statement. Well, that's not true - you can drop the table and recreate it - but ALTER COLUMN is clearly superior :)