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

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 2

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