SQL exclude a column using SELECT except columnA FROM tableA

Question

We all know that to select all columns from a table  we can use  SELECT   FROM tableA   Is there a way to exclude column s  from a table without specifying all the columns   SELECT    except columnA  FROM tableA   The only way that I know is to manually specify all the columns and exclude the unwanted column  This is really time consuming so I m looking for ways to save time and effort on this  as well as future maintenance should the table has more less columns

User · Answer

No   Maintenance-light best practice is to specify only the required columns   At least 2 reasons    This makes your contract between client and database stable  Same data  every time Performance  covering indexes   Edit  July 2011    If you drag from Object Explorer the Columns node for a table  it puts a CSV list of columns in the Query Window for you which achieves one of your goals

User · Answer

That what I use often for this case   declare  colnames varchar max      select  colnames  colnames     name from syscolumns where object id tablename  id and name not in  column3 column4   SET  colnames RIGHT  colnames LEN  colnames -1     colnames looks like column1 column2 column5

User · Answer

I do not know of any database that supports this  SQL Server  MySQL  Oracle  PostgreSQL   It is definitely not part of the SQL standards so I think you have to specify only the columns you want   You could of course build your SQL statement dynamically and have the server execute it  But this opens up the possibility for SQL injection

User · Answer

Yes it s possible  but not recommended    CREATE TABLE contact  contactid int  name varchar 100   dob datetime  INSERT INTO contact SELECT 1   Joe    1974-01-01   DECLARE  columns varchar 8000   SELECT  columns   ISNULL  columns              QUOTENAME column name  FROM INFORMATION SCHEMA COLUMNS WHERE TABLE NAME    contact  AND COLUMN NAME  lt  gt   dob  ORDER BY ORDINAL POSITION  EXEC   SELECT      columns     FROM contact     Explanation of the code    Declare a variable to store a comma separated list of column names  This defaults to NULL  Use a system view to determine the names of the columns in our table  Use SELECT  variable    variable       FROM to concatenate the column names  This type of SELECT does not not return a result set  This is perhaps undocumented behaviour but works in every version of SQL Server  As an alternative you could use SET  variable    SELECT     FOR XML PATH      to concatenate strings  Use the ISNULL function to prepend a comma only if this is not the first column name  Use the QUOTENAME function to support spaces and punctuation in column names  Use the WHERE clause to hide columns we don t want to see  Use EXEC   variable   also known as dynamic SQL  to resolve the column names at runtime  This is needed because we don t know the column names at compile time

User · Answer

DECLARE  SQL VARCHAR max    TableName sysname    YourTableName   SELECT  SQL   COALESCE  SQL               Name  FROM sys columns WHERE OBJECT ID   OBJECT ID  TableName  AND name NOT IN   Not This    Or that     SELECT  SQL    SELECT      SQL     FROM      TableName  EXEC   SQL    UPDATE   You can also create a stored procedure to take care of this task if you use it more often  In this example I have used the built in STRING SPLIT   which is available on SQL Server 2016   but if you need there are pleanty of examples of how to create it manually on SO   CREATE PROCEDURE  usp select without   schema name sysname   N dbo    table name sysname   list of columns excluded nvarchar max    separator nchar 1    N    AS BEGIN  DECLARE    SQL nvarchar max     full table name nvarchar max    CONCAT  schema name  N      table name     SELECT  SQL   COALESCE  SQL               QUOTENAME  Name    FROM sys columns sc  LEFT JOIN STRING SPLIT  list of columns excluded   separator  ss ON sc  name    ss  value   WHERE sc OBJECT ID   OBJECT ID  full table name  N u    AND ss  value  IS NULL    SELECT  SQL   N SELECT      SQL   N  FROM      full table name   EXEC  SQL  END   And then just   EXEC  usp select without    table name   N Test Table    list of columns excluded   N ID  Date  Name

User · Answer

no there is no way to do this  maybe you can create custom views if that s feasible in your situation  EDIT May be if your DB supports execution of dynamic sql u could write an SP and pass the columns u don t want to see to it and let it create the query dynamically and return the result to you  I think this is doable in SQL Server atleast

User · Answer

In summary you cannot do it  but I disagree with all of the comment above  there  are  scenarios where you can legitimately use a    When you create a nested query in order to select a specific range out of a whole list  such as paging  why in the world would want to specify each column on the outer select statement when you have done it in the inner

User · Answer

If you want to exclude a sensitive case column like the password for example  I do  this to hide the value   SELECT        as password FROM tableName

User · Answer

If you re using mysql-workbench  you can right click on the table explorer and click on  Send to SQL editor- Select all statement    It sends a statement like  Select col1  col2     from tablename    Then remove those that you don t need

User · Answer

A colleage advised a good alternative    Do SELECT INTO in your preceding query  where you generate or get the data from  into a table  which you will delete when done   This will create the structure for you   Do a script as CREATE to new query window   Remove the unwanted columns  Format the remaining columns into a 1 liner and paste as your column list   Delete the table you created    Done     This helped us a lot

User · Answer

This won t save time on loading from the database   But  you could always unset the column you don t want in the array it s placed in   I had several columns in a table but didn t want one particular  I was too lazy to write them all out in the SELECT statement      i 0   row array   array     while  row   mysqli fetch assoc  result        row array  i   row    unset  row array  i   col name        i

User · Answer

The proposed answer  stored procedure  from BartoszX didn t work for me when using a view instead of a real table   Credit for the idea and the code below  except for my fix  belongs to BartoszX   In order that this works for tables as well as for views  use the following code   SET ANSI NULLS ON GO  SET QUOTED IDENTIFIER ON GO  CREATE PROCEDURE  dbo   select without   schema name sysname   N dbo    table name sysname   list of columns excluded nvarchar max    separator nchar 1    N    AS BEGIN  DECLARE    SQL nvarchar max     full table name nvarchar max    CONCAT  schema name  N      table name     SELECT  SQL   COALESCE  SQL               QUOTENAME  Name    FROM sys columns sc  LEFT JOIN STRING SPLIT  list of columns excluded   separator  ss ON sc  name    ss  value   WHERE sc OBJECT ID   OBJECT ID  full table name   AND ss  value  IS NULL    SELECT  SQL   N SELECT      SQL   N  FROM      full table name   EXEC  SQL  END GO

User · Answer

Sometimes the same program must handle different database stuctures  So I could not use a column list in the program to avoid errors in select statements     gives me all the optional fields  I check if the fields exist in the data table before use  This is my reason for using   in select   This is how I handle excluded fields   Dim da As New SqlDataAdapter  select   from table   cn  da FillSchema dt  SchemaType Source  Dim fieldlist As String      For Each DC As DataColumn In DT Columns    If DC ColumnName ToLower  lt  gt  excludefield Then     fieldlist   fieldlist  amp   DC Columnname  amp         End If   Next

User · Answer

If anyone here is using MySql like I was use this   CREATE TABLE TempTable AS SELECT   FROM  YourTable    ALTER TABLE TempTable  DROP COLUMN  YourColumn    SELECT   FROM TempTable   DROP TABLE TempTable

User · Answer

In SSMS there is an easier way with IntelliSense and Aliasing   Try this  Right-Click in the text editor and make sure IntelliSense is enabled    Type the query with an alias  SELECT t   FROM tablename t    Go the text t  ast  and delete the  ast   and SSMS will auto-list the columns of the f aliased table   You can then quickly specify only the columns you want  w o having to use SSMS to write a select to another script and then do more copy paste operations  I use this all the time

User · Answer

Basically  you cannot do what you would like - but you can get the right tools to help you out making things a bit easier   If you look at Red-Gate s SQL Prompt  you can type  SELECT   FROM MyTable   and then move the cursor back after the      and hit  lt TAB gt  to expand the list of fields  and remove those few fields you don t need   It s not a perfect solution - but a darn good one   -  Too bad MS SQL Server Management Studio s Intellisense still isn t intelligent enough to offer this feature         Marc

User · Answer

Well  it is a common best practice to specify which columns you want  instead of just specifying    So you should just state which fields you want your select to return

User · Answer

The automated way to do this in SQL  SQL Server  is   declare  cols varchar max    query varchar max   SELECT   cols   STUFF                              SELECT DISTINCT          name             FROM sys columns             where object id                     select top 1 object id from sys objects                 where name    MyTable                            and name not in   ColumnIDontWant1    ColumnIDontWant2               FOR XML PATH                1  2                   SELECT  query    select      cols     from MyTable     EXEC   query

User · Answer

If you are using SQL Server Management Studio then do as follows    Type in your desired tables name and select it Press Alt F1 o p shows the columns in table  Select the desired columns Copy  amp  paste those in your select query Fire the query    Enjoy

User · Answer

The following is to generate the list of columns to use in a query  not to automate the query  as this was not specified   SELECT      GROUP CONCAT          CONCAT       COLUMN NAME                SEPARATOR    n        AS  cols  FROM information schema  COLUMNS  WHERE  TABLE SCHEMA     db  AND  TABLE NAME     table name here  AND  COLUMN NAME  NOT IN   exclude col1    exclude col2    Will produce   included col1    included col2    included col3   You can then copy this to use in a query  SELECT      included col1        included col2        included col3  FROM db table name here

User · Answer

Right click table in Object Explorer  Select top 1000 rows  It ll list all columns and not    Then remove the unwanted column s   Should be much faster than typing it yourself   Then when you feel this is a bit too much work  get Red Gate s SQL Prompt  and type ssf from tbl  go to the   and click tab again

User · Answer

You can get SQL Complete from devart com  which not just expands the   wildcard just like SQL Prompt from Red Gate does  as described  in cairnz s answer   but also provides a column picker drop down with checkboxes in which you can check all the columns that you want in the select list and they will be inserted automatically for you  and if you then uncheck a column it will be automatically removed from the select list

User · Answer

I did it like this and it works just fine  version 5 5 41      prepare column list using info from a table of choice SET  dyn colums    SELECT REPLACE  GROUP CONCAT  COLUMN NAME      column name to remove       FROM  INFORMATION SCHEMA   COLUMNS  WHERE   TABLE SCHEMA   database name  AND  TABLE NAME   table name       set sql command using prepared columns SET  sql   CONCAT  SELECT     dyn colums    FROM table name       prepare and execute PREPARE statement FROM  sql  EXECUTE statement

User · Answer

You could create a view that has the columns you wish to select  then you can just select   from the view

User · Answer

The best way to solve this is using view you can create view with required  columns and retrieve data form it  example  mysql gt  SELECT   FROM calls   ---- ------------ ---------    id   date         user id    ---- ------------ ---------     1   2016-06-22         1      2   2016-06-22      NULL      3   2016-06-22      NULL      4   2016-06-23         2      5   2016-06-23         1      6   2016-06-23         1      7   2016-06-23      NULL    ---- ------------ ---------  7 rows in set  0 06 sec   mysql gt  CREATE VIEW C VIEW AS     - gt      SELECT id date from calls  Query OK  0 rows affected  0 20 sec   mysql gt  select   from C VIEW   ---- ------------    id   date          ---- ------------     1   2016-06-22      2   2016-06-22      3   2016-06-22      4   2016-06-23      5   2016-06-23      6   2016-06-23      7   2016-06-23    ---- ------------  7 rows in set  0 00 sec

User · Answer

You can use REGEX Column Specification   The following query selects all columns except ds and hr SELECT  ds hr      FROM sales

User · Answer

You can try it this way     Get the data into a temp table    SELECT   INTO  TempTable FROM YourTable    Drop the columns that are not needed    ALTER TABLE  TempTable DROP COLUMN ColumnToDrop    Get results and drop temp table    SELECT   FROM  TempTable DROP TABLE  TempTable

User · Answer

No  there isn t any way to do that  and there is no good reason to do it   When selecting data you should never use    you should always specify the fields that you want  The reason is that you want the query to work the same even if you later add another field to the table  Also you specify the order of the fields in the result so that rearranging fields in the table doesn t change the result   The same would of course apply to   except if it was possible to do

User · Answer

Is there a way to exclude column s  from a table without specifying   all the columns    Using declarative SQL in the usual way  no   I think your proposed syntax is worthy and good  In fact  the relational database language  Tutorial D  has a very similar syntax where the keywords ALL BUT are followed by a set of attributes  columns    However  SQL s SELECT   already gets a lot a flak   Guffa s answer here is a typical objection   so I don t think SELECT ALL BUT will get into the SQL Standard anytime soon    I think the best  work around  is to create a VIEW with only the columns you desire then SELECT   FROM ThatView

User · Answer

I know this is a little old  but I had just run into the same issue and was looking for an answer  Then I had a senior developer show me a very simple trick   If you are using the management studio query editor  expand the database  then expand the table that you are selecting from so that you can see the columns folder   In your select statement  just highlight the referenced columns folder above and drag and drop it into the query window  It will paste all of the columns of the table  then just simply remove the identity column from the list of columns

User · Answer

Wouldn t it be simpler to do this   sp help  lt table name gt    -Click on the  Column name  column  Copy  Paste  creates a vertical list  into a New Query window and just type commas in front of each column value    comment out the columns you don t want    far less typing than any code offered here and still manageable

User · Answer

If you re using PHP you just do your query and then you can unset an specific element    sql    SELECT   FROM          your query        result    conection- gt query  sql      execute your query      row cnt    result- gt num rows      if   row cnt  gt  0            while   row    result- gt fetch object                  unset  row- gt your column name      Exclude column from your fetch              data      row    echo json encode  data      or whatever

User · Answer

Postgres sql has a way of doing it   pls refer  http   www postgresonline com journal archives 41-How-to-SELECT-ALL-EXCEPT-some-columns-in-a-table html  The Information Schema Hack Way  SELECT  SELECT      array to string ARRAY SELECT  o            c column name         FROM information schema columns As c             WHERE table name    officepark               AND  c column name NOT IN  officeparkid    contractor                    FROM officepark As o  As sqlstmt   The above for my particular example table - generates an sql statement that looks like this  SELECT o officepark o owner o squarefootage      FROM officepark As o

User · Answer

Depending on the size of your table  you can export it into Excel and transpose it to have a new table in which the columns of original table will be the rows in new table  Then take it back into your SQL database and select the rows according to the condition and insert them into another new table  Finally export this newer table to Excel and do another transpose to have your desired table and take it back to your SQL database   Not sure if tranpose can be done within SQL database  if yes then it will be even easier   Jeff

User · Answer

If you don t want to write each column name manually you can use Script Table As by right clicking on table or view in SSMS like this     Then you will get whole select query in New Query Editor Window then remove unwanted column like this     Done

User · Answer

A modern SQL dialect like BigQuery proposes an excellent solution      SELECT   EXCEPT ColumnNameX   ColumnNameY          This is a very powerful SQL syntax to avoid a long list of columns that need to be updated all the time due to table column name changes  And this functionality is missing in the current SQL Server implementation  which is a pity  Hopefully  one day  Microsoft Azure will be more data scientist friendly   Data scientists like to be able to have a quick option to shorten a query and be able to remove some columns  due to duplication or any other reason    https   cloud google com bigquery docs reference standard-sql query-syntax select-modifiers

User · Answer

I know this question is old  but I hope this can still be helpful The answer is inspired by a discuss from SQL Server Forums  You can make this a stored procedure  It can also be modified to add more than one except fields   DECLARE  SQL NVARCHAR MAX  SELECT  SQL   COALESCE  SQL                 name from sys columns where name not in   colName1   colName2   and object id    Select id from sysobjects where name    tblName   SELECT  SQL    SELECT      SQL     FROM      tblName  EXEC sp executesql   SQL

User · Answer

In Hive Sql you can do this  set hive support quoted identifiers none  select        unwanted col1 unwanted col2 unwanted col3       from database table  this gives you the rest cols

User · Answer

If we are talking of Procedures  it works with this trick to generate a new query and EXECUTE IMMEDIATE it   SELECT LISTAGG  column name         WITHIN GROUP  ORDER BY column id  INTO var list of columns FROM ALL TAB COLUMNS WHERE table name    PUT HERE YOUR TABLE  AND column name NOT IN   dont want this column   neither this one   etc column

User · Answer

Like the others have said there is no way to do this  but if you re using Sql Server a trick that I use is to change the output to comma separated  then do   select top 1   from table   and cut the whole list of columns from the output window  Then you can choose which columns you want without having to type them all in

User · Answer

In SQL Management Studio you can expand the columns in Object Explorer  then drag the Columns tree item into a query window to get a comma separated list of columns

[sql] SQL exclude a column using SELECT * [except columnA] FROM tableA?

Examples related to sql

Examples related to sql-server

Examples related to tsql