How to select all the columns of a table except one column?

Question

I have nearly 259 columns I cant mention 258 columns in SELECT statement.

Is there any other way to do it?

You've already got a table with 259 columns. Why do you care about selecting one less? You've already made a monster of a database and a monster of a result set, one column won't make a difference. — Luaan, Mar 17 '15 at 09:24
No, that's not possible. You have to list every column (or write a function that does this for your). But many SQL clients can help you to "generate" that list automatically through auto completion. Plus which DBMS are you using? Postgres? Oracle? — , Mar 17 '15 at 09:27
@GiriPrasad In Management studio, you can right click the table, select `Script table as` -> `Select to` -> `New query window`. This will generate a select with all the columns, and you can just get rid of the one you don't want. — Luaan, Mar 17 '15 at 13:15
possible duplicate of [SQL exclude a column using SELECT \* \[except columnA\] FROM tableA?](http://stackoverflow.com/questions/729197/sql-exclude-a-column-using-select-except-columna-from-tablea) — Bridge, Mar 24 '15 at 15:03

score 78 · Accepted Answer · answered Mar 17 '15 at 09:22

78

You can use this approach to get the data from all the columns except one:-

Insert all the data into a temporary table
Then drop the column which you dont want from the temporary table
Fetch the data from the temporary table(This will not contain the data of the removed column)
Drop the temporary table

Something like this:

SELECT * INTO #TemporaryTable FROM YourTableName

ALTER TABLE #TemporaryTable DROP COLUMN Columnwhichyouwanttoremove

SELECT * FROM #TemporaryTable 

DROP TABLE #TemporaryTable

answered Mar 17 '15 at 09:22

Rahul Tripathi

168,305
31
280
331

1

How do you know which DBMS Giri is using? – Mar 17 '15 at 09:27
18

@a_horse_with_no_name:- I dont know thats why I said its just an *approach*! – Rahul Tripathi Mar 17 '15 at 09:28
1

Thanks for response. seems to be a useful solution – Giri Prasad Mar 17 '15 at 12:46
7

I must admit, this is a novel solution. However, this can place quite a load on a busy server every time the query needs to be executed. And all because one db developer has an attack of the lazies. "I can't mention 258 columns in select statement" indeed! – TommCatt Mar 18 '15 at 04:55
16

In fact, in thinking about it some more, I think I would have a long sit-down with any programmer who executed such code. Of course, I would also like to have a long discussion with whoever designed such a table and then again with the analyst who requested such a query. Heads will roll, people! – TommCatt Mar 18 '15 at 04:59
1

Excellent answer. For **dropping multiple columns**, refer to [this answer](https://stackoverflow.com/a/15823531/6340496). – S3DEV Feb 13 '20 at 12:20
2

How can it be an accepted answer ? I'm waiting to see a guy who ask why this query is so long with tables of billions of row... @TommCatt, I would sit next to you. – Erwan Daniel Oct 07 '20 at 13:14
Great solution for many use-cases, but not all. Like a lot of what we do :) – 8forty Sep 10 '21 at 17:46
@TommCatt, erwan daniel, you know some tables have less than 100 rows ? – AndrewR Jan 04 '23 at 11:44

score 21 · Answer 2 · answered Mar 18 '15 at 04:51

21

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.

answered Mar 18 '15 at 04:51

TommCatt

5,498
1
13
20

does this view last after end of connection? – João Pimentel Ferreira Jul 04 '22 at 15:38

score 18 · Answer 3 · edited Jul 04 '22 at 18:48

18

This is not a generic solution, but some databases allow you to use regular expressions to specify the columns.

For instance, in the case of Hive, the following query selects all columns except ds and hr:

SELECT `(ds|hr)?+.+` FROM sales

edited Jul 04 '22 at 18:48

João Pimentel Ferreira

14,289
10
80
109

answered Mar 01 '19 at 13:35

Fatore

586
8
10

2

I was a serious `hive` developer and did not know about this - v cool. – WestCoastProjects Dec 20 '19 at 20:29
3

This regex is flawed. If you want to exclude two columns `day` and `day_hour`, `(day|day_hour)?+.+` will still match `day_hour` column. That's because regex engine is eager on `|`. Although changing the order to `(day_hour|day)?+.+` can solve this issue, the better method is using negative lookahead, `(?!(day|day_hour)$).+`. – lovetl2002 Jun 28 '20 at 06:47
1

I am using Hive but somehow it's giving me an error saying `Invalid table alias or column reference`. It seems like it's reading "(colname)?+.+" as a literal column name. Any solution for this? Thanks. – Bowen Liu Sep 14 '20 at 23:54
For this to work, hive.support.quoted.identifiers should be set to none. – Danyal Jul 10 '23 at 22:16
@lovetl2002 that's a very important catch! – Danyal Jul 10 '23 at 22:39
In case anyone use spark or databricks, we need enable regex for column within 2 backticks \`......\` : spark.conf.set("spark.sql.parser.quotedRegexColumnNames", "true") – Xiaowei Song Aug 31 '23 at 13:30

score 12 · Answer 4 · answered Mar 24 '15 at 12:02

You can get the column name details from sys.columns table

Try the following query:

SELECT * FROM SYS.COLUMNS 
WHERE object_id = OBJECT_ID('dbo.TableName') 
AND [Name] <> 'ColumnName'

DECLARE @sql as VARCHAR(8000)
SET @sql = 'SELECT '

SELECT @sql += [Name] + ', ' FROM SYS.COLUMNS 
WHERE object_id = OBJECT_ID('dbo.TableName') 
AND [Name] <> 'ColumnName'

SELECT @sql += ' FROM Dbo.TableName'

EXEC(@sql)

score 10 · Answer 5 · answered Mar 06 '19 at 02:04

10

I just wanted to echo @Luann's comment as I use this approach always.

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.

answered Mar 06 '19 at 02:04

Ashish Gupta

14,869
20
75
134

Simple solution I also ended up with - even simpler is using the "Select top 1000 Rows" though. :) – Johny Skovdal Jan 29 '20 at 12:27

score 8 · Answer 6 · answered Oct 29 '20 at 09:27

8

You can retrieve the list of column name by simple query and then remove those column by apply where query like this.

SELECT * FROM (
SELECT COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = N'TableName'
) AS allColumns
WHERE allColumns.COLUMN_NAME NOT IN ('unwantedCol1', 'unwantedCol2')

answered Oct 29 '20 at 09:27

Bikash Mahato

81
1
1

3

It can be done with a single where clause, no need to select from select – astentx Oct 29 '20 at 10:58

score 7 · Answer 7 · edited May 23 '17 at 12:10

7

There are lot of options available , one of them is :

 CREATE TEMPORARY TABLE temp_tb SELECT * FROM orig_tb;
 ALTER TABLE temp_tb DROP col_x;
 SELECT * FROM temp_tb;

Here the col_x is the column which u dont want to include in select statement.

Take a look at this question : Select all columns except one in MySQL?

edited May 23 '17 at 12:10

Community

1
1

answered Mar 17 '15 at 09:23

Sagar Joon

1,387
14
23

Vlad Bezden · Answer 8 · 2019-06-21T11:53:24.643

6

If you are using DataGrip you can do the following:

Enter your SELECT statement SELECT * FROM <your_table>;
Put your cursor over * and press Alt+Enter
You will get pop up menu with Expand column list option
Click on it and it will convert * with full list of columns
Now you can remove columns that you don't need

Here is a link for an example on how to do it.

edited Jun 21 '19 at 11:53

answered Jun 21 '19 at 11:42

Vlad Bezden

83,883
25
248
179

this is a great solution – Kevin Kreps Jan 19 '22 at 14:15

score 4 · Answer 9 · answered Jan 01 '16 at 11:02

Without creating new table you can do simply (e.g with mysqli):

get all columns
loop through all columns and remove wich you want
make your query

$r = mysqli_query('SELECT column_name FROM information_schema.columns WHERE table_name = table_to_query');

$c = count($r); while($c--) if($r[$c]['column_name'] != 'column_to_remove_from_query') $a[] = $r[$c]['column_name']; else unset($r[$c]);

$r = mysqli_query('SELECT ' . implode(',', $a) . ' FROM table_to_query');

score 1 · Answer 10 · answered Sep 08 '17 at 04:35

Try the following query:

DECLARE @Temp NVARCHAR(MAX); 
DECLARE @SQL NVARCHAR(MAX);

SET @Temp = '';
SELECT @Temp = @Temp + COLUMN_NAME + ', ' FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME ='Person' AND COLUMN_NAME NOT IN ('Id')  

SET @SQL = 'SELECT ' + SUBSTRING(@Temp, 0, LEN(@Temp)) +' FROM [Person]';
EXECUTE SP_EXECUTESQL @SQL;

score 0 · Answer 11 · answered Jan 20 '17 at 21:06

0

In your case, expand columns of that database in the object explorer. Drag the columns in to the query area.

And then just delete one or two columns which you don't want and then run it. I'm open to any suggestions easier than this.

answered Jan 20 '17 at 21:06

Abhinav Vanam

31
4

score 0 · Answer 12 · answered Aug 09 '23 at 08:51

You may select data to temporary table, alter table by removing columns you don't want and drop temporary table afterwards. EG. I am taking data from HumanResources.Employee, creating temporary table with that data, droping LoginID column and droping temporary table afterwards:

SELECT *
INTO temp_table
FROM HumanResources.Employee

ALTER TABLE temp_table
DROP COLUMN LoginID
GO

SELECT *
FROM temp_table
DROP TABLE temp_table

score -1 · Answer 13 · answered Apr 26 '18 at 09:45

-1

Only one way to achieve this giving column name. There is no other method found. You must have to list all column name

answered Apr 26 '18 at 09:45

yasir kk

161
5
9

1

While what you say may be the case in general there are multiple answers describing [corner] cases where the intended result can be achieved. – WestCoastProjects Dec 20 '19 at 20:30

How to select all the columns of a table except one column?

13 Answers13

Linked