When you think ASP, think...
Recent Articles
All Articles
ASP.NET Articles
ASPFAQs.com
Message Board
Related Web Technologies
User Tips!
Coding Tips

Sections:
Sample Chapters
Commonly Asked Message Board Questions
JavaScript Tutorials
MSDN Communities Hub
Official Docs
Security
Stump the SQL Guru!
XML Info
Information:
Feedback
Author an Article
ASP ASP.NET ASP FAQs Message Board Feedback
Print this page.
The SQL Guru Answers your Questions...


Today's question comes from Jeremy C.:

SQL 6.5 -- how do you delete a column from a table?

Jeremy C.

Jeremy,

Let's assume your original table is called foo. To remove a column from foo, you'll need to perform the following steps:

  • Create a new table, say, foo_new.
  • Use an INSERT..SELECT to copy the data from foo to foo_new.
  • Drop foo.
  • Rename foo_new to foo: EXEC sp_rename foo_new, foo

That's all there is to it. If there is a lot of data in the original table, be careful that your log is sized appropriately, batch the inserts, or bcp the data out of the original table and back into the new table.

Or consider upgrading to SQL 7.0. ALTER TABLE DROP COLUMN sure is nice! :)

Sean


Read Other SQL Guru Questions


ASP.NET [1.x] [2.0] | ASPMessageboard.com | ASPFAQs.com | Advertise | Feedback | Author an Article