Alter a column length

Question

I need to alter the length of a column "column_length" in say more than 500 tables and the tables might have no of records ranging from 10 records to 3 or 4 million records.

1) The column may just be a normal column *create table test(column_length varchar(10))*

2) The column might contain non-clustered index on it.

*create table test(column_length varchar(10))*

*CREATE UNIQUE NONCLUSTERED INDEX column_length_ind ON test (column_length)*

3) The column might contain PRIMARY KEY clustered index on it

*create table test(column_length varchar(10))*

*alter table test add primary key clustered index on column_length*

4) The column might be a composite primary key

5) The column might have a foreign key reference

In short the column "column_length" might be anything.

All i need is to create scripts to alter the length of the column_length from varchar(10) to varchar(50)

should i drop the indexes before altering and then recreate them? What about the primary key and foreign key?

Through my research and testing I figured out that...

I can just alter the column's length with out dropping the primary key or any indexes but have to drop and recreate the foreign key alone.

Is this a right assumption? I need a verification on this..

Any help would be great

Thanks guys!!!

Answer 1

Yes you should be able to just modify the columns. From my experience it is faster to leave the index and primary key in place.

Answer 2

Likely you will need to do alter column on the foreign key tables as well to increase the size. SO first you drop the fk constraint, then fix the forign kkey fields, then fix the primary key field then put the constraints back on.