CODEWITHSUNDEEP
sql-server
John Gomez
John Gomez

Reputation: 21

Changing columns from INT to BIGINT in different tables

I have a situation where I need to change a column from INT to BIGINT in different tables. The problem is that some of these tables have constraint, indexes, keys and data in it; and some of them are related one to another. I created a series of steps to do to perform this job, and I will appreciate your opinions.

  1. Identify the columns on the tables. SELECT SCHEMA_NAME(schema_id) AS schema_name, t.name AS table_name, c.name AS column_name FROM sys.tables AS t INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID WHERE 1=1 AND c.name LIKE '%columnname%' ORDER BY schema_name, table_name;

  2. Pick a table and generate script to create and drop on each constraint, keys and index.

  3. Create a new table with identical structure as the picked table but creating the desired column as a BIGINT instead of INT.

  4. Insert the rows from the old table into the new table.

  5. Run the DROP scripts for each constraint, keys and index in the old table.

  6. DELETE all the rows in the old table.

  7. Alter the old table to modify the desired column from INT to BIGINT.

  8. Run the CREATE scripts for each constraint, keys and index in the old table.

  9. Insert the rows from the new table to the old table.

  10. Go to step 2, chose another table and repeat.

Do you think this is a good plan? I also appreciate any recommendation for a backup plan.

Upvotes: 2

Views: 4990

Answers (1)

Juan Carlos Oropeza
Juan Carlos Oropeza

Reputation: 48187

Why not just alter your column datatype? Convert INT -> BIGINT shouldnt be any problem

ALTER TABLE dbo.yourTable
   ALTER COLUMN yourColumnName BIGINT

Upvotes: 2

Related Questions