How to drop multiple columns in SQL Server

Question

I have multiple columns

Dbname: dbo.Test

Column's name (example) :

ABC_1 | DEF_2 | GHI_3 | JKL_4 | MNO_5 | PQR_6 | STU_7

How can I write queries that can drop column GHI_3 until STU_7 using a loop?

Answer 1

You don't need to write a loop to do this, one way is using sys.columns table to get all the columns which you want to drop.

Let say you want to drop all columns except 'Col11' and 'Col2' in that case you can write your query like following.

declare @dropstmt as nvarchar(max) ='alter table Test  drop column ' 
                          +  stuff((select ', ' + quotename(name) 
             from   
             (
                select c.name 
                from sys.columns c
                    JOIN sys.tables t ON c.object_id = t.object_id
                where t.name = 'test'
                and c.name  not in('col1','col2')
             )t
             for xml path(''), type).value('.', 'NVARCHAR(MAX)'), 1, 1, ''); 
print @dropstmt
exec sp_executesql @dropstmt