drop columns

Question

drop columns

HBK_4700

SSCrazy Eights

Points: 8932
More actions
February 15, 2012 at 8:08 am

#255759

Hi,
I am trying create script to drop the some of the columns (NO PK columns) in some 20 tables.
ALTER TABLE Table
DROP COLUMN Column111
GO
Is there any way I can achieve this single script, and also is there any impact on database if I drop some columns in different tables, I mean what are things i need to consider before drop, we have some 300 tables in database.
Any script to drop multiple columns in one table?
Appreciate your help, thank you.

Viewing 6 posts - 1 through 5 (of 5 total)

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

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

I've never used this before, but this snippet from the ALTER TABLE documentation suggests you can drop more than one column at once:

| DROP

{

[ CONSTRAINT ] constraint_name

[ WITH ( <drop_clustered_constraint_option> [ ,...n ] ) ]

| COLUMN column_name

} [ ,...n ]

You need to be careful if the table you're dropping from is involved in replication, or if it has a foreign key constraint on it.

John

sumitagarwal781 Default port Points: 1493 More actions · Answer 2

Column names are same for all thirty table which you want to drop?

java

HBK_4700 SSCrazy Eights Points: 8932 More actions · Answer 3

John Mitchell-245523 (2/15/2012)
I've never used this before, but this snippet from the ALTER TABLE documentation suggests you can drop more than one column at once:
| DROP
{
[ CONSTRAINT ] constraint_name
[ WITH ( <drop_clustered_constraint_option> [ ,...n ] ) ]
| COLUMN column_name
} [ ,...n ]
You need to be careful if the table you're dropping from is involved in replication, or if it has a foreign key constraint on it.
John

Thanks John, so is this script to drop more than one column in single table? or drop columns in multiple tables?

HBK_4700 SSCrazy Eights Points: 8932 More actions · Answer 4

sumitagarwal781 (2/15/2012)
Column names are same for all thirty table which you want to drop?

columns names are different.

for ex:

Table 1

Column22, Column 25

Table 2

COlumn 4, Column 12, Column 23, Column 30

Table 3

Column 11, Column 14, Column 22, Column 29, Column 45,

.....

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 5

It isn't a script at all. It's a snippet from the documentation for ALTER TABLE... which name should suggest to you that it's to alter one table at a time. I don't think there's any command to drop columns from different databases in one go, althout you could write some SQL to generate the script to do so.

http://msdn.microsoft.com/en-us/library/ms190273.aspx

John