more efficient sql

From: Jeff Kish <jeff.kish_at_mro.com>
Date: Mon, 06 Nov 2006 21:25:22 -0500
Message-ID: <ngrvk2pitbv39bdallemkj91pr3fs969t2@4ax.com>

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 Received on Mon Nov 06 2006 - 20:25:22 CST