CODEWITHSUNDEEP
sql-server
user21968
user21968

Reputation: 1165

Alter a column length

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))
  1. 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)
  1. 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

  1. The column might be a composite primary key

  2. 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 without dropping the primary key or any indexes but have to drop and recreate the foreign key alone.

Is this assumption right?

Upvotes: 3

Views: 2769

Answers (2)

HLGEM
HLGEM

Reputation: 96650

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.

Upvotes: 1

J.J.
J.J.

Reputation: 4872

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.

Upvotes: 2

Related Questions