Deleting Colums from a Table
-
This is probably a really dumb question... But I got a request to delete several columns that are no longer being used from a table (sql server 7.0). What I did was make a copy of the table using the export function and now I'm thinking all I have to do is go into DESIGN TABLE from enterprise manager and delete the rows. Is this the correct way to go about doing this and will this affect the users????
-
I don't recommend that you use EM for that -- What it will do is create a new table minus the columns you've deleted, then copy all of the data over into the new table, then swap the tables using sp_rename. Highly inefficient.
Instead, use:
ALTER TABLE YourTable
DROP COLUMN YourColumn
--
Adam Machanic
whoisactive -
Thanks much. So I'll go ahead and make a copy of my table for safe-keeping and then use ALTER TABLE and DROP COLUMN
Thanks.
-
The ALTER TABLE and DROP COLUMN did not work for me (similar issue to yours encountered last week) as I have indexes in the table. Therefore I backed up my database from the server, restored it locally (I always have a workstation copy of the live data), scripted the desired table on the server, amended said script to remove the offending columns, deleted the table on the server (having kicked all users off for a few minutes:whistling
, re-generated the table using my script (including triggers, indexes, etc.) and finally, imported the data from my local copy of the table (minus the offending columns). It worked a treat!Diarmuid
-
Dumb Questions DO NOT EXCIST !!!!
try;
select <all columns you need>
into #temp_table
from original_table
drop original_table
select *
into original_table_remake
from #temp_table
GKramer
The Netherlands
-
Thank you one and all !!
-
No offense Guus Kramer but why not use enterprise manager if you're gonna do it the same way it does it but without the User interface?? Other than can keep the scripts for later use... but EM can save the execution script too...
-
Remi,
as we say; "There a many ways to Rome".
When I started within ICT I learned it the scripting way. Nowadays its a click and ready application doing the job for you and anyone can do this. But does everyone knows what happens underneath such a tool??? I think not....
So I intend to do plain scripting as much as possible so at least I know exactly what I am doing and know where to look if something goes wrong ( debugging ).
Thanks for replying ( this can be a new thread discussing scripting or clicking 🙂 )
GKramer
The Netherlands
-
I totally agree with that.. Microsoft should put a big flashing sign that it's copying/deleting all the content of the table underneath it all.
But then again why would DBAS exists if everything was so simple as point and click :-).
-
Remi,
But then again why would DBAS exists if everything was so simple as point and click :-).
To keep a certain group of "thinkers" off the street preventing a riot ???
Just kiddin'
They click - we think ( and restore the mess they made ?? )
Guus
-
Well I'm restoring my own mess here... I'm the only one involved in IT so I do pretty much everything except advanced networking.
So I got no time left for rioting :-).
-
I'm one of 3 DBA's here ( in a total group of 40 operational guys (networking , exchange, hardware , OS related etc etc ( we are doing the whole of Europe from here ))) and I'm busy cleaning up the mess other (once) made.......
I also have no time to play out on the street...
Guus
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply