Menu
How to select all the columns of a table except one column?
I have nearly 259 columns I cant mention 258 columns in SELECT statement.
Is there any other way to do it?
641
The information_schema. COLUMNS table holds all information about the columns in your MySQL tables. To exclude columns, you use the REPLACE() and GROUP_CONCAT() functions to generate the column names you wish to include in your SELECT statement later.
The SQL EXCEPT operator is used to return all rows in the first SELECT statement that are not returned by the second SELECT statement. Each SELECT statement will define a dataset. The EXCEPT operator will retrieve all records from the first dataset and then remove from the results all records from the second dataset.
You can use this approach to get the data from all the columns except one:- 1 Insert all the data into a temporary table 2 Then drop the column which you dont want from the temporary table 3 Fetch the data from the temporary table (This will not contain the data of the removed column) 4 Drop the temporary table More ...
Usually, we use * to select all the columns but if we have to select all the columns except one column, then we have to write all the required columns one by one. Here I am going to show how we can achieve it without writing all the columns one by one.
Just right click on the table > Script table as > Select to > New Query window. You will see the select query. Just take out the column you want to exclude and you have your preferred select query. Is this answer outdated?
One way of doing it is to name all the columns in the SELECT statement. But if you have more than 10 columns in a single table, then specifying all column names but one is too tedious.
You can use this approach to get the data from all the columns except one:-
Something like this:
SELECT * INTO #TemporaryTable FROM YourTableName ALTER TABLE #TemporaryTable DROP COLUMN Columnwhichyouwanttoremove SELECT * FROM #TemporaryTable DROP TABLE #TemporaryTable Create a view. Yes, in the view creation statement, you will have to list each...and...every...field...by...name.
Once.
Then just select * from viewname after that.
38
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
