How can you upgrade a datatype from a 32-bit Integer to a 64-bit Integer in SQL for all foreign keys?

Question

For basically every SQL database out there, how would you upgrade your data types and have all the foreign keys linked to that data type be upgraded?

For example, let's say I have ScientificResult is a 32-bit integer. Now I want ScientifcResult to be a 64-bit integer. ScientificResult is also a foreign key for a few other tables but they are declared as 32-bit integers.

Is there a generic way to do this?

Answer 1

I'd say the generic way would be to drop all the keys, change the column datatype, and then recreate all of the keys.

Answer 2

Generate scripts using INFORMATION_SCHEMA.COLUMNS and the various constraint tables.

1. Drop all the foreign keys
2. Upgrade all the tables (ALTER TABLE ALTER COLUMN)
3. Re-create all the foreign keys

If the tables are huge, step 2 and 3 can take a LONG time.