2

I am managing a database in SQL Server 2008. Across about 15 tables, I have 1600 VARCHAR columns that need to be changed to NVARCHAR.

I was able to generate all the appropriate ALTER TABLE statements with a query, but now I am faced with the problem of running them.

My approach has been to just paste the 1600 ALTER TABLE statements and try to run them, however this seems to be too big to run practically. If I step away from my desk for too long, I am disconnected from SQL Server and the process stops (no columns are changed).

Is there a smarter way to run this?

  • 1
    Change your timeout so you are not disconnected. Go to the server and run the command. – paparazzo Jul 13 '16 at 21:05
  • 1
    Run one table worth at a time? Schedule a job to run overnight using a service account that won't be disconnected? – Molenpad Jul 13 '16 at 21:11
  • 1
    Is having > 100 NVARCHAR tables per table a good idea? Not to mention any other INTs, DATES &c. Can you (not sure about the exact MS SQL command syntax) extract a database creation script, use sed or whatever editor you want to change VARCHAR to NVARCHAR. Then drop your database and recreate it using your new script? Your system will be down for a while (depending on size of db), but it will be an "all-in-one-go" solution which should be quicker than a piecemeal approach. – Vérace Jul 14 '16 at 4:22
4

Realizing that this is a complete redesign of your intent but why not script out the change for each table? Ie create a new table, copy the data over, re-create all indexes, security etc.

The easiest way to do this would be to use the designer, make your changes, don't hit save, but instead right click on the background of the designer and request "generate change script". Copy that out and you can then make changes to it as needed. That way you only have 15 scripts to run.

I did this recently for a particularly large table (~700gb), using the script to create the new table and using an SSIS package to copy the data. Then I ran the remainder of the script to generate the indexes etc. This can be surprisingly fast depending on your needs.

Now if you don't want to do it that way you do have a couple of other options.

You could put GO statements and use PRINT to print a status between each ALTER. Then when you run your script you can tell where it failed. It will only roll back the last statement you ran and you can run from that point forward.

Last option I can think of is to remote to the server itself (not considered a best practice) and run the script there. That way even if you lose your connection to the server the script will continue to run regardless.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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