SELECT * EXCEPT

Question

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.

Answer 1

Stay away from SELECT *, you are setting yourself for trouble. Always specify exactly which columns you want. It is in fact quite refreshing that the "feature" you are asking for doesn't exist.

Answer 2

I believe the rationale for it not existing is that the author of a query should (for performance sake) only request what they're going to look at/need (and therefore know what columns to specify) -- if someone adds a couple more blobs in the future, you'd be pulling back potentially large fields you're not going to need.

Answer 3

As others are saying: SELECT * is a bad idea.

Some reasons:

1. Get only what you need (anything more is a waste)
2. Indexing (index what you need and you can get it more quickly. If you ask for a bunch of non-indexed columns, too, your query plans will suffer.

Answer 4

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

Answer 5

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

Answer 6

declare @sql nvarchar(max)
        @table char(10)
set @sql = 'select '
set @table = 'table_name'

SELECT @sql = @sql + '[' + COLUMN_NAME + '],'
FROM   INFORMATION_SCHEMA.Columns
WHERE  TABLE_NAME = @table
   and COLUMN_NAME <> 'omitted_column_name'
SET    @sql = substring(@sql,1,len(@sql)-1) + ' from ' + @table

EXEC (@sql);