Search code examples
sql-serverddl

DROP all columns from an existing SQL Server table

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?

Solution

  • 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.