Select all cols in a table except for 2 explicitly specified cols without typing out the majority of the column names?

From: dana <dana_at_work_at_yahoo.com>
Date: Wed, 22 Jul 2009 10:01:39 -0700 (PDT)
Message-ID: <eba48dd4-8b49-4d2e-a816-7804f3a2498e_at_o6g2000yqj.googlegroups.com>

In Oracle SQL (or any other dialect), if there an easy way to select all cols in a table except for 2 explicitly specified cols, without having to type the majority of the column names?

Example 1: Select all columns

select a.*
from all_tables a;

Example 2: Pseudo SQL to select all columns but two in a table having many columns (e.g. 47 columns out of 49 columns for this example table in 10g):

select all columns except for MIN_EXTENTS and MAX_EXTENTS from all_tables a;

Is this a job for "SQL writing SQL", using PL/SQL and a limiting subquery like:

select column_name
from all_tab_cols
where owner = 'SYS'
and table_name = 'ALL_TABLES'
and column_name not in ('MIN_EXTENTS', 'MAX_EXTENTS');

... or is this a much simpler solution (one that doesn't involve manually copy-pasting 47 columns)?

Thanks.

Dana Received on Wed Jul 22 2009 - 12:01:39 CDT