CODEWITHSUNDEEP
sqlsql-server
IvanJazz
IvanJazz

Reputation: 773

Changing varchar to nvarchar for all table columns in a database

Is there a way to fully traverse all tables in the database and changing all the varchar datatype to nvarchar datatype?

The simple SQL below does not work as intended due to constraints from indexing, unique keys and what not.

ALTER TABLE [CUS].[Customers] 
    ALTER COLUMN [CustomerNo] NVARCHAR(500);

I tried changing them from SSMS via the GUI way and the profiler actually shows what it does to truly change the datatype.

This involves a lot of steps and it is quite cumbersome if I were to do it manually.

Can anyone suggest a more flexible solution to my problem?

Upvotes: 0

Views: 4224

Answers (1)

user170442
user170442

Reputation:

Alternatively you could:

  1. Script whole database without data
  2. Alter column definitions from VARCHAR to NVARCHAR
  3. Use either SSIS or SQL to transfer data

Pros:

  • It could have a less significant impact on the source database
  • You could minimize the impact even more if you can run this on separate server

Cons:

  • You would have to transfer all data, which takes time
  • You would have to make sure you have enough disk space on server for database data, index data and log file

Depending on the transfer method you choose and size of your tables, the log file might grow huge even if you are in simple recovery mode. This is the worst case scenario when you transfer a lot of data in one huge transaction.

As a side comment: I agree with @Igor - do not change datatype unless you absolutely have to.

Upvotes: 1

Related Questions