Changing primary key column type from varchar to integer

Question

A while ago we migrated an old database to SQL, surprisingly we found out that some primary key values contain text instead of an integer. This is causing the output column type to be of varchar. We would like to convert this to an integer, because we want to use auto increment.

Our database consists of quite a few tables and many more rows, so we can't do this by hand. There are also foreign keys, which we have to update those as well.

We used SSIS to import the data from the old database, and already used some data conversion blocks to fix some typing problems, however I couldn't find any blocks/functionality to solve this problem. I also considered writing a t-sql script, but because of all the foreign keys, I hope there is a better way to do this.

So, what's the best way to go about this?

Answer 1

Hunker down and write the script. What you're describing isn't difficult just a little time consuming.

I'd normally recommend to install SQL Server Data Tools; create a new database project by pointing it at the existing database and doing an import (this populates all the tables, views, procedures, indexes, etc). That will then likely build with little or no changes, and you can then make changes in the designers and see what the change script that it outputs is like.

In this case it's not a complete solution because SSDT will usually abort when it finds data in the tables when you're making large changes. But it might give you something to compare against insofar as dropping and recreating all the dependencies goes.

• Ste Bov: It is one thing that you'll have to just do, one script that'll help is select id from @table where isnumeric(id) =0 this'll pick up any records that arent a numeric format, if anything in there isn't then the alter table command will fail
• Randolph West: ISNUMERIC isn't perfect either.