Alter table column data type from samllint to int to all columns in a databasePosted: September 25, 2015
You need to drop keys and make the alter data type and reapply all keys back.
Here is a sample process with some script references:
Take a FULL backup of your database
Step 1: Prepare create and drop scripts for all table primary keys using this script:
Step 2: Prepare drop and re-create scripts for all foreign keys:
Step 3: Prepare alter statements:
SELECT ‘Alter table ‘+ [TABLE_SCHEMA]+’.’+[TABLE_NAME] +’ ALTER COLUMN ‘+ [COLUMN_NAME] +’ int’
You are ready to execute these scripts in sequence:
Drop primary keys;
Drop foreign keys;
Alter table column data type from samllint to Int;
Recreate primary keys;
Recreate foreign keys.