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.

Answer 1

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

Answer 2

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

Answer 3

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

Answer 4

A modern SQL dialect like BigQuery, Databricks proposes an excellent solution.

SELECT * EXCEPT(ColumnNameX, [ColumnNameY, ...])
FROM TableA

This is a very powerful SQL syntax to avoid a long list of columns that must be constantly updated due to table column name changes. And this functionality is missing in the current SQL Server implementation, which is a pity. Hopefully, this will someday become SQL standard and make it more data scientist-friendly.

Data scientists like a quick option to shorten a query and remove some columns (due to duplication or any other reason).

https://cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax#select-modifiers

https://docs.databricks.com/sql/language-manual/sql-ref-syntax-qry-select.html#syntax

DuckDB uses EXCLUDE instead EXCEPT https://duckdb.org/docs/sql/query_syntax/select

Answer 5

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);

Answer 6

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

Answer 7

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 <> 'dob'
ORDER BY ORDINAL_POSITION

EXEC ('SELECT ' + @columns + ' FROM contact')

Explanation of the code:

1. Declare a variable to store a comma separated list of column names. This defaults to NULL.
2. Use a system view to determine the names of the columns in our table.
3. 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.
4. 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.
5. Use the WHERE clause to hide columns we don't want to see.
6. 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.

Answer 8

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.

Answer 9

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 <TAB> 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

Answer 10

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';

Answer 11

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;

Answer 12

If you are using SQL Server Management Studio then do as follows:

1. Type in your desired tables name and select it
2. Press Alt+F1
3. o/p shows the columns in table.
4. Select the desired columns
5. Copy & paste those in your select query
6. Fire the query.

Enjoy.

Answer 13

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

Answer 14

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.

Answer 15

The best way to solve this is using view you can create view with required columns and retrieve data form it

example

mysql> 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> CREATE VIEW C_VIEW AS
    ->     SELECT id,date from calls;
Query OK, 0 rows affected (0.20 sec)

mysql> 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)

Answer 16

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');

Answer 17

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?

Answer 18

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

Answer 19

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.

Answer 20

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

Answer 21

Actually snowflake just released exclude so now you'd just:

SELECT * EXCLUDE [columnA,columnB,...] FROM tableA

Answer 22

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

Answer 23

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.

Answer 24

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.

Answer 25

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

Answer 26

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;

Answer 27

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 <> excludefield Then
    fieldlist = fieldlist &  DC.Columnname & ","
   End If
  Next

Answer 28

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

Answer 29

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.

Answer 30

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

Answer 31

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

Answer 32

Try this...

=QUERY(TRANSPOSE(QUERY('Data'!A1:AH,"SELECT * ",1)),"SELECT * WHERE Col1 <> 'Column 
 name'",1)

Answer 33

DECLARE @Columns NVARCHAR(MAX)='',@SQL NVARCHAR(MAX)
SELECT @Columns=CASE WHEN @Columns='' THEN name ELSE @Columns+','+name END  
FROM sys.columns 
WHERE object_ID=(sELECT id FROM sysobjects WHERE name='TheTableName') and NAME!='WithoutColumnName'

SELECT @SQL='SELECT '+@Columns+' FROM dbo.TheTableName'
EXEC sp_execute @SQL

Answer 34

Of course as the other they've said you need to implement with dynamic sql. This is my implementation:

SET NOCOUNT ON

DECLARE @Table NVARCHAR(100) = 'Table' --Table to Select
DECLARE @ExcludeColumns AS TABLE (ColumnName VARCHAR(255))
INSERT INTO @ExcludeColumns VALUES ('ExcludedColumn1'),('ExcludedColumn2') --Excluded columns

DECLARE @SelectedColumns NVARCHAR(MAX) = ''
SELECT 
    @SelectedColumns += CASE WHEN LEN(@SelectedColumns) = 0 THEN '' ELSE ',' END + '[' + COLUMN_NAME + ']' 
FROM 
    INFORMATION_SCHEMA.COLUMNS 
WHERE 
    TABLE_NAME = @Table AND COLUMN_NAME NOT IN (SELECT ColumnName FROM @ExcludeColumns)

DECLARE @sqlStatement NVARCHAR(MAX) = 'SELECT ' + @SelectedColumns + ' FROM [' + @Table + ']'
PRINT @sqlStatement
EXEC (@sqlStatement)

Answer 35

In SSMS there is an easier way with IntelliSense and Aliasing. Try this

1. Right-Click in the text editor and make sure IntelliSense is enabled.
2. Type the query with an alias [SELECT t.* FROM tablename t].
3. 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.

Answer 36

Wouldn't it be simpler to do this:

sp_help <table_name>

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

Answer 37

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

Answer 38

If you have many columns and you want to exclude only one columns.You can use this bellow command Select * exclude(column name) from Table

Answer 39

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

Answer 40

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

Answer 41

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.

Answer 42

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++;
}

Answer 43

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;

Answer 44

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.

Answer 45

Easy solution. In SSMS, find your table, right click it and Script Table As, then Select To. The query will be written out with every column listed. Comment out or delete the column you don't want. Very fast and easy solution to ignoring a column.

Answer 46

You can use REGEX Column Specification.

The following query selects all columns except ds and hr SELECT (ds|hr)?+.+ FROM sales

Answer 47

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->query($sql); // execute your query
    $row_cnt = $result->num_rows;   

if ($row_cnt > 0) {
        while ($row = $result->fetch_object()) {
            unset($row->your_column_name); // Exclude column from your fetch
            $data[] = $row;
}
echo json_encode($data); // or whatever