Exclude a column using SELECT * [except columnA] FROM tableA?

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

A modern SQL dialect like BigQuery 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 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 have 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

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

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.

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

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

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

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;

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

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.

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

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

Try this...

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

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

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)

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

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

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.

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.

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

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