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?
- drop columns or data in the columns? does it have any foreign key dependencies to other tables? Does it form part of the index.. a lot to consider before you go dropping columns!Harry– Harry2019-02-28 03:35:20 +00:00Commented Feb 28, 2019 at 3:35
- @Harry drop the columnsken– ken2019-02-28 03:36:48 +00:00Commented Feb 28, 2019 at 3:36
- 1OK.. why a loop and not on one go? you can go alter test drop column GHI_3,STU_7Harry– Harry2019-02-28 03:49:11 +00:00Commented Feb 28, 2019 at 3:49
- @Harry this is actually the example columns. My exact data consists up to 50 columns :D That is why I want to use the loopken– ken2019-02-28 04:28:12 +00:00Commented Feb 28, 2019 at 4:28
Add a comment |
6 Answers
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 5 Comments
ken
When I uncomment print @dropstmt it display error "Must declare the scalar variable "@cols".
PSK
it was a typo, it should be '@dropstmt' instead of '@cols'. I have updated the answer.
ken
It display error "Could not find stored procedure ''. @PSK
PSK
when you are getting this error? in execute statement??
PSK
I have modified the answer, can you try once
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 
1 Comment
ken
Thank you but this is actually the example columns. My exact data consists up to 50 columns :D That is why I want to use the loop
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 
Comments
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.
Comments
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.
2 Comments
ken
I try to use the DeleteColumnRange procedure, it shows "Commands completed successfully". But when I try to SELECT* the table, there is nothing change. Is there something I've missed? @Rasnick
Nicolas Hevia
Check: stackoverflow.com/questions/8395245/… also, did you create the SP in the correct DB? Are you calling the SP from the correct DB with the correct table name and column names? I just checked it and it works fine for any number of columns
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) 