1

I need to change all the varchar columns to the nvarchar type. I've generated a script with the help from this thread SQL Server - script to update database columns from varchar to nvarchar if not already nvarchar.

However this script cannot change the composite primary key columns. We have many composite keys in our table so this cannot be done manually.

Is there any way to script the change from varchar composite keys to nvarchar?

Community
  • 1
  • 1
kor_
  • 1,510
  • 1
  • 16
  • 36
  • 1
    Not easily - you would have to 1) find all PK with a varchar column in them, then 2) drop all foreign key constraints referencing them, 3) drop the PK, 4) change the column type, 5) recreate the PK and 6) recreate all FK constraints... it can all be scripted, but it's a heck of a lot of work.... – marc_s Oct 04 '11 at 06:50
  • What @marc_s says except I say it is fairly easy. Time-consuming and tedious, though. Perhaps someone has already has a tool to automate this...? – onedaywhen Oct 04 '11 at 07:18

2 Answers2

2

Do you have a build process for your databases? Do you have a Source Control system? If you do then I would suggest you make the changes by search and replace in source control, rebuild the database and then use a schema comparison tool (as in Visual Studio Database Projects or RedGate Schema Compare) to generate a change script.

If you don't have a build process or source control then you can script your database using Management Studio (right-click the database, select All Tasks > Generate Scripts).

nvogel
  • 24,981
  • 1
  • 44
  • 82
0

I decided to do this partially manually. First I manually changed all the primary keys from varchar to nvarchar (using Management Studio). Then I used the script to change all the other (non-constrainted) columns to nvarchar. After that I changed all the views, stored procs and triggers using find-replace. Finally I compared the database with the production one using Red Gate SQL Compare.

I also had to split the final script to smaller parts, because some of the tables were too large to be rebuilt at the same time (transaction log filled the entire drive).

kor_
  • 1,510
  • 1
  • 16
  • 36