Charles Hooper wrote:
> Jeff Kish wrote:
> > Hi.
> >
> > I'm trying to create a table that contains for the user rows that reflect all
> > the tables that contain matching column names.
> >
> > I am using this but it has duplicate in it, and also takes over a minute to
> > run.
> >
> > Is there a spiffier way of making this table from the user_tab_columns table?
> >
> > insert into usetabcolumns2TempTable ( select a.table_name TABLE_NAME_START,
> > a.column_name COLUMN_NAME, b.table_name TABLE_NAME_END from user_tab_columns
> > a, user_tab_columns b where b.table_name != a.table_name and b.column_name =
> > a.column_name )
> >
> > I'm targetting Oracle 9 onwards.
> >
> > Much Appreciated,
> > Jeff
> > Jeff Kish
>
> I am not quite sure why you would want to do this, but, you should be
> able to do as you would like using analytical functions. For example:
> SELECT
> TABLE_NAME,
> COLUMN_NAME,
> MIN(TABLE_NAME) OVER (PARTITION BY COLUMN_NAME ORDER BY TABLE_NAME)
> TABLE_NAME_START,
> MAX(TABLE_NAME) OVER (PARTITION BY COLUMN_NAME ORDER BY TABLE_NAME)
> TABLE_NAME_END,
> COUNT(*) OVER (PARTITION BY COLUMN_NAME) NUM_TABLES
> FROM
> USER_TAB_COLUMNS;
>
> MIN, MAX, and COUNT in this case provide you with the first table name,
> last table name, and the number of tables containing the column, when
> grouped by the column name.
>
> If you only want cases where a column is used in more than one table,
> you can slide the above into an inline view like this:
> SELECT
> *
> FROM
> (SELECT
> TABLE_NAME,
> COLUMN_NAME,
> MIN(TABLE_NAME) OVER (PARTITION BY COLUMN_NAME ORDER BY TABLE_NAME)
> TABLE_NAME_START,
> MAX(TABLE_NAME) OVER (PARTITION BY COLUMN_NAME ORDER BY TABLE_NAME)
> TABLE_NAME_END,
> COUNT(*) OVER (PARTITION BY COLUMN_NAME) NUM_TABLES
> FROM
> USER_TAB_COLUMNS)
> WHERE
> NUM_TABLES>1;
>
> Slight reformatting should give you what you want. There likely is a
> more efficient method.
>
> Charles Hooper
> PC Support Specialist
> K&M Machine-Fabricating, Inc.
You can also use a shorter form that does not use analytical functions,
but whether it will work depends on exactly what you are trying to do
with the data:
SELECT
MIN(TABLE_NAME) TABLE_NAME_START,
COLUMN_NAME,
MAX(TABLE_NAME) TABLE_NAME_END
FROM
USER_TAB_COLUMNS
GROUP BY
COLUMN_NAME
HAVING
COUNT(*)>1;
Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc.
Received on Mon Nov 06 2006 - 21:20:40 CST
