?
Solved

delete a column from a table in SQL

Posted on 1998-06-24
6
Medium Priority
?
570 Views
Last Modified: 2012-05-07
I don't remember the SQL syntax for delete a column !
0
Comment
Question by:verly
6 Comments
 
LVL 7

Accepted Solution

by:
Victor Spiridonov earned 150 total points
ID: 1091615
There is NO SQL syntax to delete a column from SQL Server table. The only way to do it is:
1. Rename Table
2.  Create new table with one column less
3. Insert data from renamed table into new table
4 Drop renamed table.
use IDENTITY_INSERT  ON/OFF if you inserting data into table with IDENITY column
0
 

Author Comment

by:verly
ID: 1091616
40 points because your response arrived fast !
0
 
LVL 1

Expert Comment

by:josephfluckiger
ID: 9889754
this appears to no longer be the case. Using Microsoft SQL server 2000. This statement worked for me:

ALTER TABLE MyTable DROP MyColumn
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 34

Expert Comment

by:arbert
ID: 9900484
I like it when people accept stupid answers....
0
 
LVL 1

Expert Comment

by:josephfluckiger
ID: 9900500
:)
0
 
LVL 6

Expert Comment

by:JohnDesautels
ID: 12484028
joseph,

Your syntax didn't work for me.

I needed to use DROP COLUMN vs just DROP:

ALTER TABLE MyTable DROP COLUMN MyColumn

I found it at:

http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/Q_21127678.html?query=delete+column+from+table&topics=42 

John
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question