Very Computer

Is there a way to delete a column from a table? I did not see anything in
ALTER TABLE or anything in the Books Online.

                                        Many thanks,
                                                Pete

~~
Time flies like an arrow, fruit flies like a banana.
^o_o^ <~v~> ~o\_/o~

Computer Science Major          University of Victoria

Pete,

Quote:> Is there a way to delete a column from a table? I did not see anything in
> ALTER TABLE or anything in the Books Online.

Q.  How can I add/amend/delete columns in SQL Server?  E.g. Int to Char,
char(5) to char(15), change NULL to NOT NULL etc.
(v1.7  1999.06.24)

A.  Under SQL 7.0 all the above are easily done with standard ANSI "ALTER
TABLE" ddl commands.  Or they can be done via the gui or supplied
stored-procedures.

With SQL 6.5 and below it is only possible to ADD a nullable column - or an
IDENTITY column which seems to work even if it's not NULLable.  For *any* other
change a new table must be created, the data copied across, and the tables
renamed around.

Certain 3rd party tools provide a gui interface to do this, that makes it look
transparent, however they are really doing all the work described above, so if
you make the change to a large table it will take a long time to do the work.

Examples of tools are :-

   Microsoft's Visual Database Tools (part of Visual Interdev Enterprise
Edition)
   SQL Programmer from www.sfi-software.com
   XCase - www.xcase.com
   Desktop DBA - www.platinum.com
   Speed Ferret

Note that there have been reports of MS VDT losing data if you amend columns on
a table and SQL does not have enough free-space to complete the task.

 Neil Pike MVP/MCSE.  Protech Computing Ltd
 (Please post ALL replies to the newsgroup only unless indicated otherwise)
 For SQL FAQ entries see
 http://go.compuserve.com/sqlserver (library 1) - latest stuff is always here
 www.ntfaq.com/sql.html
 http://www.swynk.com/faq/sql/sqlserverfaq.asp

Pete,

In SQL 7.0 the syntax is:
    ALTER TABLE <table> DROP COLUMN <column>
In SQL 6.5 the only way to do it (as far as I know) is to drop the table and
recreate it.

HTH,
Amalia