How to change all columns from one type to another, changing all FK columns as well?

Question

In an inherited DB we found that there are around 25 tables with a primary key defined as tinyint.

For a few of those, it's an imminent problem, since this optimistic optimization clashes with the fact that we'll (in some cases, very soon) need more rows for these tables than what tinyint allows.

For other tables, the problem lies in the near future, since it has been decided we'll switch providers and DB engine to a mostly compatible RDBMS, but that lacks tinyint support...

Each of these tables has as well between 2 to 6 foreign key relationships pointing to them, so not only we have to change these columns but the FK ones as well.

Could it be possible to somehow script this change in masse, changing all columns where name = id and type = tinyint (e.g. to smallint or even int) and at the same time alter all the tables with FK referencing these columns so they match as well?

I've seen this for changing all columns from one type to another; but I have yet to deal with the foreign-key situation.

Answer 1

This might work:

1. (backup db just in case)
2. DISABLE FKs.
3. Using a suitable SELECT into information_schem.columns, construct the ALTER TABLE statements.
4. Enable FKs.