Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
add comment

2 Answers 2

up vote 4 down vote accepted

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.

share|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
add comment

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

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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