asked on

Pitfalls of changing length of column

We have, somehow, came up with a problem whereby several developers have designed tables using a column with the same name/purpose but with different lengths.

I really need to make them all the same but need to know the pitfalls of doing so and also would like to be able to see if there are any PKs, FKs or constraints on the particular field because I would have to drop and recreate some due to modifications of the length of the column.

This column is in 81 tables. This is the different ways it is defined:
char(9) - 2 tables
char(10) - 18 tables {This was original data type}
char(15) - 2 tables
char(25) - 1table

nvarchar(20) - 2 tables
nvarchar(50) - 32 tables
nvarchar(100) - 9 tables

varchar(10) - 4 tables
varchar(20) - 1 table
varchar(25) - 5 tables
varchar(50) - 5 tables

What would be the best way to go about this with the least trouble?

Guy Hengel [angelIII / a3]

what is the data in this?
you will have to drop/recreate foreign keys indeed.

char->varchar: ensure you put a UPDATE with RTRIM() to make the trailing spaces go away for sure.
char/varchar(x) -> char/varchar(y) with y < x : well, obviously you had to check before if the y is long enough for all the data in that column

nvar/char - var/char -> check out if there are not special characters

Eddie Shipman

ASKER

How would I loop through the sys.columns with the column name and alter all those columns to be the same? I figured I'd also have to drop and recreate the keys and constraints, too.

Eddie Shipman

ASKER

As I understand it, this is the sequence that I need to follow to get this column changed:
1. drop all foreign keys containing that column
2. drop each index before altering the table
3. alter the table and trim the column contents
4. recreate the index on the table
5. recreate the foreign key that was defined for that table

I have one table that has about 9 FKs into it on this column. So...
I drop all 9, drop this table's index, modify this table, then recreate
this table's index. Then after I modify each of the 9 referenced
tables, I recreate the FK into this table. Does this sound correct?

ASKER CERTIFIED SOLUTION

Guy Hengel [angelIII / a3]

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer