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 <joel-garry_at_home.com>
Date: Wed, 22 Jul 2009 10:43:38 -0700 (PDT)
Message-ID: <640f50c0-e851-455e-825d-ac0011b4ff09_at_j9g2000prh.googlegroups.com>
On Jul 22, 10:01 am, dana <dana_at_w..._at_yahoo.com> 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
Date: Wed, 22 Jul 2009 10:43:38 -0700 (PDT)
Message-ID: <640f50c0-e851-455e-825d-ac0011b4ff09_at_j9g2000prh.googlegroups.com>
On Jul 22, 10:01 am, dana <dana_at_w..._at_yahoo.com> 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.
jg
-- _at_home.com is bogus. http://www3.signonsandiego.com/stories/2009/jul/22/apple-profits-15-downturn/?uniontribReceived on Wed Jul 22 2009 - 12:43:38 CDT