Sql-server – Change column data type across database

datatypessql serversql-server-2008type conversion

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.

Best Answer

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.

Related Question