Deleting rest of the columns after a certain column in transact SQL

Question

Let's say I have a 3 table with columns Col1, col2, col3, col4, col5, col6, col7,col8........col56 I want to delete all the columns in all the tables after Col4 dynamically.

I know below query doesn't make sense as there are large number of columns

Alter table Drop column Col5, col6, col7,....,Col56

Can somebody please suggest any alternate method that I could implement to accomplish this in MS-SQL.

You can create a dynamic sql statement starting from metadata in system view INFORMATION_SCHEMA.COLUMNS — Andrea

Larnu Larnu · Accepted Answer · 2019-03-20T13:02:58

Assuming that you only have columns called Col1, Col2, ... Col56, and that are in the "correct" order (ascending my numerical value) you could do something like:

CREATE TABLE YourTable (Col1 int,
                        Col3 int,
                        Col4 int,
                        Col5 int,
                        Col6 int,
                        Col7 int,
                        Col8 int,
                        Col9 int,
                        Col10 int);
SELECT *
FROM  dbo.YourTable;
GO

DECLARE @SQL nvarchar(MAX);
DECLARE @TableName sysname = N'YourTable';

SET @SQL = N'ALTER TABLE ' + QUOTENAME(@TableName) + N' DROP COLUMN ' + 
           STUFF((SELECT N',' + QUOTENAME(C.COLUMN_NAME)
                  FROM INFORMATION_SCHEMA.COLUMNS C
                  WHERE C.TABLE_NAME = @TableName
                    AND C.ORDINAL_POSITION > 4
                    --AND C.COLUMN_NAME LIKE N'Col%' --uncomment if needed
                  ORDER BY C.ORDINAL_POSITION
                  FOR XML PATH(N''),TYPE).value(N'.',N'nvarchar(MAX)'),1,1,N'') + N';';
PRINT @SQL;

EXEC sp_executesql @SQL;
GO
SELECT *
FROM  dbo.YourTable;

GO
DROP TABLE YOurTable;

DB<>Fiddle

Deleting rest of the columns after a certain column in transact SQL

1 Answers