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  

Answer 2

You can use this query to delete the multiple columns from the table.

alter table Test
    drop column GHI_3, JKL_4, MNO_5, PQR_6, STU_7

Answer 3

As alternative to solution on dropping the columns directly with Alter Statement , if you want to make use of loop, you can use cursor as well.

Create a test table

Create table testdropcols   
(Col1 varchar(20)
,col2 varchar (30)  
,col3 varchar (30) 
,col4 varchar(30) 
,col5 varchar(30) 
,col6 varchar(30) 
,col7 varchar(30)
,col8 varchar(30)
,col9 varchar(30)
,col10 varchar(30))  

Use systables and syscolumns for current database to get all the columns for a given table, and select few columns just for test.

    Declare @tablename varchar(30) , @colname varchar(30), @rownum int 

DECLARE Curstest CURSOR
  for 
select Table_name,columnname,rownum from (
select  st.name as Table_name, sc.name as columnname, Row_number() over (partition by  st.name order by sc.name) rownum   from sys.tables st 
join sys.columns sc on st.object_id = sc.object_id 
where st.name = 'testdropcols'  ) z 
where z.rownum between 5 and 10  
Open Curstest 
Fetch next from Curstest into @tablename, @colname, @rownum 
while @@FETCH_STATUS = 0 
Begin 
declare @sql varchar(max)

set @sql = 'ALTER TABLE '+@tablename+'   Drop column  '+@colname+''

Print @sql 

Exec(@sql)

Print('Dropping column '+ @colname  + ' from ' + @tablename+ ' for rownumber ' +cast(@rownum as varchar(10))) 
Fetch next from Curstest into @tablename, @colname, @rownum 
end 

Close Curstest 
DEALLOCATE Curstest 

With the print statement inside cursor you get the output in this way with each execution.

  ALTER TABLE testdropcols   Drop column  col4
Dropping column col4 from testdropcols for rownumber 5
ALTER TABLE testdropcols   Drop column  col5
Dropping column col5 from testdropcols for rownumber 6
ALTER TABLE testdropcols   Drop column  col6
Dropping column col6 from testdropcols for rownumber 7
ALTER TABLE testdropcols   Drop column  col7
Dropping column col7 from testdropcols for rownumber 8
ALTER TABLE testdropcols   Drop column  col8
Dropping column col8 from testdropcols for rownumber 9
ALTER TABLE testdropcols   Drop column  col9
Dropping column col9 from testdropcols for rownumber 10

If you select the testdropcols you will have the columns which did not exist in the drop statement in

Col1 Col2 Col3 Col10 

Answer 4

In SQL Server, for dropping multiple columns of a table considering columns has constraints also, this is how can you do that

alter table dbo.Test
    drop column GHI_3, STU_7

Syntax:

DROP { [ CONSTRAINT ] constraint_name | COLUMN column_name } [ ,...n ] 

And there is no need to do this in loop, it can be done in a single statement.

Answer 5

Adding this because other answers didn't actually used a loop. I don't know how many times you would use something like this though... It surely can be improved

USE Dbname;
go
CREATE PROCEDURE DeleteColumnRange
    @table_name varchar(100),
    @first varchar(100),
    @last varchar(100)
AS   
    SET NOCOUNT ON
    declare @range_start int
    declare @range_end int

    SELECT ORDINAL_POSITION, COLUMN_NAME INTO #columns_to_delete
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_NAME LIKE @table_name

    set @range_start = (SELECT ORDINAL_POSITION
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_NAME LIKE @table_name AND COLUMN_NAME LIKE @first)

    set @range_end = (SELECT ORDINAL_POSITION
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_NAME LIKE @table_name AND COLUMN_NAME LIKE @last)

    DECLARE @result NVARCHAR(max)

    IF @range_end > @range_start
        DECLARE @cnt INT = @range_start

        WHILE @cnt < @range_end
        BEGIN
            SET @cnt = @cnt + 1
            SET @result = (SELECT COLUMN_NAME from #columns_to_delete where ORDINAL_POSITION = @cnt)
            print ('ALTER TABLE ' + @table_name + ' DROP COLUMN ' + @result)
        END;
GO 

Usage:

DeleteColumnRange @table_name = 'Test', @first = 'GHI_3', @last = 'STU_7'

Returns:

ALTER TABLE Test DROP COLUMN JKL_4
ALTER TABLE Test DROP COLUMN MNO_5
ALTER TABLE Test DROP COLUMN PQR_6

Just copy and execute, safer than dropping columns.

Answer 6

I have provided a sample script to delete a list of columns from a table. You don't need a loop, as you can delete multiple columns in a single DROP COLUMN statement.

USE tempdb
go

CREATE TABLE dbo.test(a int, b int, c int)

DECLARE @TableName SYSNAME = N'dbo.test' -- table holding columns to drop
DECLARE @ColumnNames NVARCHAR(4000) = N'b,c' -- columns to drop
DECLARE @sqlDropColumnStmt NVARCHAR(4000)
SET @sqlDropColumnStmt = N'ALTER TABLE ' + @TableName + N' DROP COLUMN ' + @ColumnNames;
EXEC(@sqlDropColumnStmt)