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

From: joel garry <>
Date: Wed, 22 Jul 2009 10:43:38 -0700 (PDT)
Message-ID: <>

On Jul 22, 10:01 am, dana <> wrote:
> 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

Well, are you asking for a dynamic thing or an easy way to limit what other users see? If the latter, use views. If the former, either play around with ..._tab_cols for the report-generator kind of thing or keep a bunch of files around with all the columns in them and grab what you need in your editor for the real ad-hoc stuff. Personally, I tend to do a combination of the latter (to make scripts, especially ETL or mass updates) and use tools that are appropriate for querying/ updating when I just need to poke around.

Other people have different requirements.


-- is bogus.
Received on Wed Jul 22 2009 - 12:43:38 CDT

Original text of this message