Alter TABLE DROP Column - multiple columns MS SQL

Question

I have several database tables that a lot of redundant columns in that contain no data. Ideally I need to look at dropping these columns.

However since there are hundreds I was looking for a way to use the DROP COLUMN command from a table lookup..

So instead of:

alter table TableName
    drop column Column1, Column2

Something like:

alter table TableName
    drop column @ColumnName

Using the @ColumnName as a lookup from a table that holds the column names of blank columns, would a cursor work with alter/drop commands?

Pரதீப் · Accepted Answer · 2017-06-23 09:06:21Z

1

You need dynamic query to drop the columns present in variable

exec('alter table TableName drop column '+@ColumnName)

Make sure the constraints are dropped before dropping the column

answered Jun 23, 2017 at 9:06

Pரதீப்

94.1k21 gold badges144 silver badges179 bronze badges

Sign up to request clarification or add additional context in comments.

1 Answer 1