SELECT * EXCEPT

Is there any RDBMS that implements something like SELECT * EXCEPT? What I'm after is getting all of the fields except a specific TEXT/BLOB field, and I'd like to just select everything else.

Almost daily I complain to my coworkers that someone should implement this... It's terribly annoying that it doesn't exist.

Edit: I understand everyone's concern for SELECT *. I know the risks associated with SELECT *. However, this, at least in my situation, would not be used for any Production level code, or even Development level code; strictly for debugging, when I need to see all of the values easily.

As I've stated in some of the comments, where I work is strictly a commandline shop, doing everything over ssh. This makes it difficult to use any gui tools (external connections to the database aren't allowed), etc etc.

Thanks for the suggestions though.

Solution 1:

As others have said, it is not a good idea to do this in a query because it is prone to issues when someone changes the table structure in the future. However, there is a way to do this... and I can't believe I'm actually suggesting this, but in the spirit of answering the ACTUAL question...

Do it with dynamic SQL... this does all the columns except the "description" column. You could easily turn this into a function or stored proc.

declare @sql varchar(8000),
    @table_id int,
    @col_id int

set @sql = 'select '

select @table_id = id from sysobjects where name = 'MY_Table'

select @col_id = min(colid) from syscolumns where id = @table_id and name <> 'description'
while (@col_id is not null) begin
    select @sql = @sql + name from syscolumns where id = @table_id and colid = @col_id

    select @col_id = min(colid) from syscolumns where id = @table_id and colid > @col_id and name <> 'description'
    if (@col_id is not null) set @sql = @sql + ','
    print @sql
end

set @sql = @sql + ' from MY_table'

exec @sql

Solution 2:

Create a view on the table which doesn't include the blob columns

Solution 3:

Is there any RDBMS that implements something like SELECT * EXCEPT?

Yes, Google Big Query implements SELECT * EXCEPT:

A SELECT * EXCEPT statement specifies the names of one or more columns to exclude from the result. All matching column names are omitted from the output.

WITH orders AS(
  SELECT 5 as order_id,
  "sprocket" as item_name,
  200 as quantity
)
SELECT * EXCEPT (order_id)
FROM orders;

Output:

+-----------+----------+
| item_name | quantity |
+-----------+----------+
| sprocket  | 200      |
+-----------+----------+

EDIT:

H2 database also supports SELECT * EXCEPT (col1, col2, ...) syntax.

Wildcard expression

A wildcard expression in a SELECT statement. A wildcard expression represents all visible columns. Some columns can be excluded with optional EXCEPT clause.

EDIT 2:

Hive supports: REGEX Column Specification

A SELECT statement can take regex-based column specification in Hive releases prior to 0.13.0, or in 0.13.0 and later releases if the configuration property hive.support.quoted.identifiers is set to none.

The following query selects all columns except ds and hr.

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

Solution 4:

DB2 allows for this. Columns have an attribute/specifier of Hidden.

From the syscolumns documentation

HIDDEN
CHAR(1) NOT NULL WITH DEFAULT 'N'
Indicates whether the column is implicitly hidden:

P Partially hidden. The column is implicitly hidden from SELECT *.

N Not hidden. The column is visible to all SQL statements.

Create table documentation As part of creating your column, you would specify the IMPLICITLY HIDDEN modifier

An example DDL from Implicitly Hidden Columns follows

CREATE TABLE T1
(C1 SMALLINT NOT NULL,
C2 CHAR(10) IMPLICITLY HIDDEN,
C3 TIMESTAMP)
IN DB.TS;

Whether this capability is such a deal maker to drive the adoption of DB2 is left as an exercise to future readers.