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.


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:


SELECT @sql += CHAR(13) + CHAR(10) + N'ALTER TABLE '
  + '.' + QUOTENAME(OBJECT_NAME([object_id])) 
  + 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.

| improve this answer | |
  • Thank you for your response. When I mean manually, I mean looking at each table in the Object Explorer, noting down the columns with datatype ntext, and executing ALTER TABLE foo ALTER COLUMN bar nvarchar(max) over and over again. I wish to avoid unnecessary manual labor if possible. – Anonymous Maximus Jul 16 '12 at 19:39
  • Automating the process will also hopefully ensure I don't do mistakes or miss/skip any columns. – Anonymous Maximus Jul 16 '12 at 19:45
  • 1
    I've updated my answer to show one way to automate this. The reason it is PRINT is so that you can preview the output and/or run commands individually. Just note that PRINT has a character limitation so you might observe the output looks truncated (the command itself is not). – Aaron Bertrand Jul 16 '12 at 19:46

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

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.