Deleting Colums from a Table

Question

Deleting Colums from a Table

potucekj

Default port

Points: 1414
More actions
November 10, 2004 at 8:05 am

#361609

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????

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Adam Machanic SSCoach Points: 15370 More actions · Answer 1

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

potucekj Default port Points: 1414 More actions · Answer 2

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.

Diarmuid Hurley SSC Veteran Points: 237 More actions · Answer 3

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

Guus Kramer Ten Centuries Points: 1031 More actions · Answer 4

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

potucekj Default port Points: 1414 More actions · Answer 5

potucekj

Default port

Points: 1414

November 11, 2004 at 5:07 am

#529754

Thank you one and all !!

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 6

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...

Guus Kramer Ten Centuries Points: 1031 More actions · Answer 7

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

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 8

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 :-).

Guus Kramer Ten Centuries Points: 1031 More actions · Answer 9

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

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 10

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 :-).

Guus Kramer Ten Centuries Points: 1031 More actions · Answer 11

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