SQL Server Delete a range of columns via query in SMSS (SQL) instead of listing them out or graphically one at a time?

oprakyz7  于 2023-08-02  发布在  其他
关注(0)|答案(2)|浏览(83)

I had to import a large CSV into SQL Server and there are a slew of columns I need to drop but don't want to list them all out or graphically right-click and delete if it's possible to just drop them all in one go.

Editing the CSV won't really work due to it being large and Excel causing some alignment issues.

I've already dropped a few columns by right-clicking and "Delete" both from the Object Explorer as well as via Design; and by using

ALTER TABLE table_name
    DROP COLUMN column56, column57, ...

I would like to be able to do something like

DROP COLUMN from column56 to column79
hgncfbus

hgncfbus1#

If your columns are really numbered (column56, column57, ...) you can use dynamic sql here.

declare @I int = 56;
declare @sql nvarchar(1000);
while @I <= 79
begin
    set @sql = N'alter table dbo.MyTable drop column ' + 
                '[column' + cast(@I as nvarchar(30)) + N']';
    exec sp_executesql @sql;
    set @I += 1;
end;

You can also delete columns using the table designer in Sql Server Management Studio ( see delete-columns-from-a-table )

yzxexxkh

yzxexxkh2#

Try generating the necessary ALTER TABLE statements dynamically from column56 to column79:

DECLARE @TableName NVARCHAR(128) = 'table_name'
DECLARE @DropColumns NVARCHAR(MAX) = ''

SELECT @DropColumns = @DropColumns + QUOTENAME(name) + ', '
FROM sys.columns
WHERE object_id = OBJECT_ID(@TableName)
    AND column_id >= (SELECT column_id FROM sys.columns WHERE object_id = OBJECT_ID(@TableName) AND name = 'column56')
    AND column_id <= (SELECT column_id FROM sys.columns WHERE object_id = OBJECT_ID(@TableName) AND name = 'column79')

SET @DropColumns = LEFT(@DropColumns, LEN(@DropColumns) - 1) -- Remove the trailing comma

DECLARE @Sql NVARCHAR(MAX) = 'ALTER TABLE ' + QUOTENAME(@TableName) + ' DROP COLUMN ' + @DropColumns
PRINT @Sql -- Optional: Print the generated SQL statement

-- Uncomment the following line to execute the dynamic SQL statement
-- EXEC sp_executesql @Sql

相关问题