How to Change datatype of all column to Unicode datatype for all base table in a database

Question

Due to storing values from language specific diacritics (Spanish, French, German) I am trying to change the datatype of a column to Unicode datatype.

• Varchar to nvarchar
• char to nchar

So all column datatype to its respective Unicode datatype.
For all tables in a specific database.

I tried like this as below:-

ALTER TABLE dbo.Employee
ALTER COLUMN FirstName NVARCHAR(255) NOT NULL

but doing for 500 tables having 60 column means i have to write 3000 times alter statement

Can it be possible to do in a single statement?

Please help

Many Thanks

Answer 1

Hi @William Patelk ,

Welcome to Microsoft Q&A!

Please refer below:

SELECT TABLE_CATALOG  
,TABLE_SCHEMA  
,TABLE_NAME  
,COLUMN_NAME  
,'ALTER TABLE ['+TABLE_SCHEMA+'].['+TABLE_NAME+'] ALTER COLUMN ['+COLUMN_NAME+'] '+  
case when DATA_TYPE='Varchar' then  'NVARCHAR(255)'  else 'NCHAR(255)'  end   
+ ' NOT NULL'as 'code'  
FROM INFORMATION_SCHEMA.COLUMNS  
where DATA_TYPE in ('Varchar','char')  

Above generates an ALTER TABLE statement for each column of all tables in one database for you, then you could execute them all at once.

Best regards
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.