DROP all columns from an existing SQL Server table

Question

I need to drop all columns in an existing table in SQL Server. It is possible to delete columns by specifying each column name. I want to delete every column without specifying column name. I am looking for something like

ALTER TABLE tblUser DROP COLUMN *;

Is there any known way to do this?

Why would you want to do that? If you just want to drop the whole table, use DROP TABLE tablename — Pavel Smirnov
– Pavel Smirnov, Commented Dec 27, 2019 at 5:53
Without columns, tables are only a name. If you are dropping all the columns, you may consider dropping the whole table with DROP TABLE tablename — STaefi
– STaefi, Commented Dec 27, 2019 at 5:58
Specify all the column names to remove.. this can be done programmatically with “dynamic SQL”, including generation from an external language. Deleting the table itself has slightly different connotations. — user2864740
– user2864740, Commented Dec 27, 2019 at 6:00
I don't think SQL Server even allows tables without columns. — user330315
– user330315, Commented Dec 27, 2019 at 6:12

Community · Accepted Answer · 2020-06-20 09:12:55Z

3

Answering your question literally, no you can't. If you try to remove the last column, SQL Server will throw the following error:

Msg 4923, Level 16, State 1, Line 12

ALTER TABLE DROP COLUMN failed because 'Id' is the only data column in table 'NoColumns'. A table must have at least one data column.

As such, if you actually want to solve your problem, whatever it is, it would be best to voice the initial problem and not the solution you decided to pursue.

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered Dec 27, 2019 at 6:07

Roger Wolf

7,7872 gold badges29 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Never Die · Accepted Answer · 2019-12-27 06:03:47Z

1

Instead remove all the columns, you could drop the table

DROP TABLE TABLENAME

Or You can mention all the column names in Alter query

ALETR TABLE TableName DROP COLUMN Column1, Column2, Column3....ColumnN

answered Dec 27, 2019 at 6:03

Never Die

3511 silver badge5 bronze badges

2 Comments

New Developer Over a year ago

Hi Thank you for the comments, In my case the columns are dynamic and I have to do another insert with different number of columns right after dropping the first set of columns to the same table. I tried dropping the table and inserting it, but it throws errors related to batch, my problem here is I need to be stay in same batch also, so a GO statement also not useful. Is there any way we stay in same batch and do a drop table tblUser; INSERT INTO tblUser select * from anotherTable

Never Die Over a year ago

@NewDeveloper Then do first add all the columns and remove all old columns using ALTER TABLE

Ali Soltani · Accepted Answer · 2023-04-10 14:33:27Z

0

SELECT COLUMN_NAME INTO #result FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME= N'tablename' AND COLUMN_NAME != 'Guid' -- or id
DECLARE @sql NVARCHAR(MAX) = '';
DECLARE @colNames NVARCHAR(MAX) = '';
SELECT @colNames += '['+ r.COLUMN_NAME + ']' +',' from #result r;
SET @colNames = LEFT(@colNames,LEN(@colNames)-1)
DROP TABLE #result;
SET @sql = 'ALTER TABLE tablename DROP COLUMN ' + @colNames;
EXEC sp_executesql @sql

answered Apr 10, 2023 at 14:33

Ali Soltani

515 bronze badges

Collectives™ on Stack Overflow

DROP all columns from an existing SQL Server table

3 Answers 3

Comments

2 Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

Comments

Related