如何在SQL Server中删除多列

Question

我有多个列

数据库名称：dbo.Test

列名（例如）：

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

如何使用循环编写查询以删除从 GHI_3 至 STU_7 的列?

- ken

---

删除列或者数据？它是否有外键依赖于其他表？它是否是索引的一部分？在你开始删除列之前需要考虑很多因素！ - Harry

@Harry 删除这些列。 - ken

好的。为什么要使用循环而不是一次完成？你可以运行以下命令：alter test drop column GHI_3,STU_7 - Harry

@Harry 这只是示例列。我的确切数据包含多达50列:D这就是为什么我想使用循环的原因。 - ken

Answer 1

你不需要编写循环来完成此操作，一种方法是使用 sys.columns 表来获取所有要删除的列。

假设您要删除除 'Col11' 和 'Col2' 之外的所有列，则可以按以下方式编写查询。

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  

- PSK

---

当我取消注释print @dropstmt时，它显示错误“必须声明标量变量“@cols”。 - ken

这是一个笔误，应该是'@dropstmt'而不是'@cols'。我已经更新了答案。 - PSK

它显示错误“无法找到存储过程''。@PSK - ken

你是在执行语句时遇到了这个错误吗？ - PSK

我已经修改了答案，你可以再试一次吗？ - PSK

Answer 2

您可以使用此查询从表中删除多个列。

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

- Mukesh Arora

---

谢谢，但这实际上是示例列。我的确切数据有多达50列 :D 这就是为什么我想使用循环的原因。 - ken

Answer 3

作为直接使用Alter语句删除列的解决方案的替代方法，如果您想利用循环，也可以使用游标。

创建一个测试表。

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))  

使用systables和syscolumns获取当前数据库中给定表的所有列，并仅选择少量列进行测试。

    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 

在游标内使用print语句，每次执行时都会以这种方式输出结果。

  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

如果您选择testdropcols，您将获得在删除语句中不存在的列。

Col1 Col2 Col3 Col10 

- Avi

Answer 4

在SQL Server中，如果要删除一个表的多个带有约束条件的列，可以按照以下步骤进行操作。

alter table dbo.Test
    drop column GHI_3, STU_7

语法：

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

而且没有必要在循环中执行此操作，可以在单个语句中完成。

- Geeky Ninja

Answer 5

添加这个是因为其他答案并没有实际使用循环。 虽然我不知道你会用这样的东西多少次... 但它肯定可以改进。

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 

使用方法：

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

返回：

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

只需复制并执行，比删除列更安全。

- Nicolas Hevia

---

我尝试使用DeleteColumnRange过程，它显示“命令已成功完成”。但是当我尝试选择*表时，没有任何变化。我错过了什么吗？@Rasnick - ken

请检查：https://stackoverflow.com/questions/8395245/the-commands-completed-successfully，另外，您是否在正确的数据库中创建了SP？ 您是否使用正确的表名和列名从正确的数据库调用SP？ 我刚刚检查过它，对于任意数量的列都可以正常工作。 - Nicolas Hevia

Answer 6

我已经提供了一个示例脚本，用于从表中删除一系列列。您无需使用循环，因为可以在单个DROP COLUMN语句中删除多个列。

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)

- Venkataraman R