50

How do I delete a column from an existing table?

Tony L.
  • 17,638
  • 8
  • 69
  • 66
Roshan
  • 707
  • 1
  • 9
  • 12
  • 2
    possible duplicate of [How to remove a column from an existing table?](http://stackoverflow.com/questions/5626344/how-to-remove-a-column-from-an-existing-table) – Aaron Kurtzhals Feb 07 '14 at 16:20
  • 2
    Actually, the other question was written 2 years after this one so that would be the duplicate. – Tony L. Apr 11 '16 at 21:03
  • Though the other question was written after this one it is getting way more views & votes. Must be a more google friendly title or something. As such I voted to close this question. – Martin Brown May 03 '18 at 14:08

5 Answers5

103

The command you're looking for is:

alter table tblName drop column columnName

where tblName is the name of the table and columnName is the name of the column, but there's a few things you may need to do first.

  • If there are any foreign key references to the column, you'll need to get rid of them first.
  • If there's an index using that column, you'll need to either get rid of it or adjust it to not use that column.

Keep in mind that the performance of this command may not necessarily be good. One option is to wait for a down-time period when you can be certain no-one will be accessing the database, rename the current table, then use create table and insert into ... select from to transfer the columns you don't want deleted.

One of the later releases of Oracle actually has a soft delete which can just marks a column as unused without removing it physically. It has the same effect since you can no longer reference it and there's a command along the lines of alter table ... drop unused columns which is meant to be run in quiet time, which does the hard work of actually removing it physically.

This has the advantage of "disappearing" the columns immediately without dragging down database performance during busy times.

paxdiablo
  • 854,327
  • 234
  • 1,573
  • 1,953
13

ALTER TABLE XXX DROP COLUMN YYY;

brenjt
  • 15,997
  • 13
  • 77
  • 118
Joakim Backman
  • 1,875
  • 13
  • 12
5

This can also be done through the SSMS GUI.

I like this method because it warns you if there are any relationships on that column and can also automatically delete those as well. As PaxDiablo states, if there are relationships, they must be deleted first.

  1. Put table in Design view (right click on table) like so:

enter image description here

  1. Right click on column in table's Design view and click "Delete Column"

enter image description here

At this point, if there are any relationships that would also need to be deleted, it will ask you if you would like to delete those as well.

Tony L.
  • 17,638
  • 8
  • 69
  • 66
  • After I had deleted the columns, I had to right click on the "Design" canvas, and select `Generate Change Script`. Then I ran that script which deleted the columns. The columns were not deleted before running that script. – jeppoo1 Dec 28 '20 at 13:10
  • @jeppoo1 OOC did you click Save after deleting the column? – Tony L. Dec 28 '20 at 15:43
  • 1
    I didn't, I wonder why that didn't even come to my mind... Now I know I can save it with CTRL+S! Thanks! – jeppoo1 Dec 30 '20 at 14:53
3

For large tables this can be very slow. It can often be a lot faster to create a new table, a duplicate of the old one but with the changes, and insert the data. Drop the old table and then rename the new table.

Unsliced
  • 10,404
  • 8
  • 51
  • 81
1
ALTER TABLE [Intake].[MER_SF_Opportunity_History] DROP COLUMN [[IntakeIsDeleted]]]
GO
Suraj Rao
  • 29,388
  • 11
  • 94
  • 103
Saravanan B
  • 21
  • 1