Alter column varchar(max) to nvarchar(max) with condition

Question

I have a column emailbody with a datatype of varchar(max), but I want to change it to nvarchar(max).

How to check the varchar(max) length in condition?

if not exists (select name from syscolumns where name = 'emailbody' and id = OBJECT_ID('emailtb') and xtype = 167 and [length] = ??)
   ALTER TABLE emailtb 
   ALTER COLUMN emailbody nvarchar(max)

Please tag dbms product used, to get better answers faster! – jarlh Jul 01 '15 at 07:28 — jarlh, Jul 01 '15 at 07:28

score 1 · Answer 1 · answered Jul 01 '15 at 07:27

1

varchar(max), nvarchar(max) and varbinary(max) are represented as having a length of -1. So:

if not exists (select name from syscolumns where name = 'emailbody' and id = OBJECT_ID('emailtb') and xtype = 167 and [length] = -1)
ALTER TABLE emailtb ALTER COLUMN emailbody nvarchar(max)

For additional details, see the documentation about sys.columns.

answered Jul 01 '15 at 07:27

Mureinik

297,002
52
306
350

2

@A.Q - so, what *does* happen? Do you get an error message? Error messages are tremendously useful, whereas your feedback of "not alter the column" isn't. – Damien_The_Unbeliever Jul 01 '15 at 08:27

score 0 · Answer 2 · edited May 23 '17 at 12:25

Altering the following Source:

select character_maximum_length    
from information_schema.columns  
where table_name = 'emailtb' AND column_name='emailbody'

will get you the length.

in your context:

if not exists (select name from syscolumns where name = 'emailbody' and id = OBJECT_ID('emailtb') and xtype = 167 and [length] = (select character_maximum_length from information_schema.columns where table_name = 'emailtb' AND column_name='emailbody'))
ALTER TABLE emailtb ALTER COLUMN emailbody nvarchar(max)

quick explanation:

An information schema view is one of several methods SQL Server provides for obtaining metadata. Information schema views provide an internal, system table-independent view of the SQL Server metadata. Information schema views enable applications to work correctly although significant changes have been made to the underlying system tables. The information schema views included in SQL Server comply with the ISO standard definition for the INFORMATION_SCHEMA.

More about information_schema can be found in this MSDN article.

There are some [fairly good arguments against using the `INFORMATION_SCHEMA` in SQL Server](https://sqlblog.org/2011/11/03/the-case-against-information_schema-views), the most relevant to this context would be the fact that computed columns are indistinguishable from normal columns, so you can't tell if the varchar(max) column you have found is computed, and thus whether the alter operation will fail. — GarethD, Jul 01 '15 at 07:38

score 0 · Answer 3 · answered Jul 01 '15 at 07:39

If you have any strings in your varchar(max) column that is longer than 1 GB - 1 byte (1'073'741'823 byte), then you will not be able to convert it to nvarchar(max).

Check like this:

SELECT emailbody, DATALENGTH(emailbody)
FROM dbo.emailtb 
WHERE DATALENGTH(emailbody) > 1073741823

If you find any rows - you'll need to either shorten those emailbody contents, or delete those rows, before being able to convert to nvarchar(max)

Alter column varchar(max) to nvarchar(max) with condition

3 Answers3