Alter table column data type from samllint to int to all columns in a database


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:

Step 0:

Take a FULL backup of your database

Step 1: Prepare create and drop scripts for all table primary keys using this script:

http://social.technet.microsoft.com/wiki/contents/articles/2321.script-to-create-or-drop-all-primary-keys.aspx

Step 2: Prepare drop and re-create scripts for all foreign keys:

https://www.mssqltips.com/sqlservertip/3347/drop-and-recreate-all-foreign-key-constraints-in-sql-server/

Step 3: Prepare alter statements:

SELECT ‘Alter table ‘+ [TABLE_SCHEMA]+’.’+[TABLE_NAME] +’ ALTER COLUMN ‘+ [COLUMN_NAME] +’ int’
FROM [INFORMATION_SCHEMA].[COLUMNS]
where [DATA_TYPE]=’smallint’

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.

Advertisements


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s