question

palum avatar image
palum asked

alter table drop column name - Tsql

I have a tables with 800 column, I have to drop 350-400 columns from each of the tables. Using the statement alter table XYZ drop column a,b,c,d................... is taking forever to drop the column on SQL Server2000 Can anyone help or give me some idea how can this be done effitiently.. thanks
sql-server-2000tsqlalter-table
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
Kev Riley avatar image
Kev Riley answered
1. select 400 cols you want to keep from XYZ into NewXYZ 2. rename XYZ to OldXYZ 3. rename NewXYZ to XYZ 4. Add indexes
4 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

WilliamD avatar image WilliamD commented ·
I think I would change the order to add indexes before the rename & drop though.
1 Like 1 ·
Kev Riley avatar image Kev Riley ♦♦ commented ·
Good point - wasn't actually thinking about indexes and I added it at the last moment. The other point to note is that if the indexes are named, such as 'PK_XYZ' then they will need to be dropped or renamed on the existing table before attempting to add them to the new table
1 Like 1 ·
Cyborg avatar image Cyborg commented ·
Make sure no users other than you are accessing the table while you rename or add\remove columns to avoid blocking.
0 Likes 0 ·
palum avatar image palum commented ·
AWESOME, THIS IS QUICK.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.