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

Question

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

User · Answer

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   https   cwiki apache org confluence display Hive LanguageManual Select LanguageManualSelect-REGEXColumnSpecification

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

If you are using DataGrip you can do the following    Enter your SELECT statement SELECT   FROM  lt your table gt   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

User · Answer

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   lt  gt   ColumnName   DECLARE  sql as VARCHAR 8000  SET  sql    SELECT    SELECT  sql     Name         FROM SYS COLUMNS  WHERE object id   OBJECT ID  dbo TableName    AND  Name   lt  gt   ColumnName   SELECT  sql      FROM Dbo TableName   EXEC  sql

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

Generate your select query with any good autocomplete editor of your database  copy and paste your select query in a reserved method  function  and return its ResultSet to your main program or do your stuff in the same method  call this method each time whenever you require

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

Examples related to sql

Examples related to sql-server