Home » SQL & PL/SQL » SQL & PL/SQL » Finding Common Columns in a set of tables
Finding Common Columns in a set of tables [message #249563] Thu, 05 July 2007 01:05 Go to next message
diku
Messages: 23
Registered: April 2007
Junior Member
Hi Folks,

I have a requirement where I need to find out the common columns in a set of multiple tables. I have developed the procedure and for a set of 3 or 4 tables it's fast enough. But if the no. of tables increases the execution time increases. Since I am using unions it's obvious. I want to know if there is any way I can speed up this proc or is there any other way to tackle this.
Any suggestions are welcome

CREATE OR REPLACE PROCEDURE column_show (p_objectlist IN VARCHAR2)
IS
   TYPE object_list IS TABLE OF VARCHAR2 (50)
      INDEX BY BINARY_INTEGER;

   v_objectlist           object_list;
   v_no_of_objects        NUMBER (10);
   v_copy_of_objectlist   VARCHAR2 (500);
   v_counter              NUMBER (10);
   v_default_query        VARCHAR2 (1000);
   v_sql_query            VARCHAR2 (1000);
BEGIN
   v_copy_of_objectlist := p_objectlist;
   v_default_query :=
                 'select COLUMN_NAME from ALL_TAB_COLS where table_name = ''';
   
/*Compute no. of objects in the input parameter*/
   SELECT   LENGTH (p_objectlist)
          - LENGTH (REPLACE (p_objectlist, ','))
          + 1
     INTO v_no_of_objects
     FROM DUAL;
   v_counter := v_no_of_objects;
   v_copy_of_objectlist := p_objectlist;

   WHILE v_no_of_objects > 1
   LOOP
      /*Populating the array*/
      SELECT SUBSTR (
                v_copy_of_objectlist,
                1,
                  INSTR (v_copy_of_objectlist, ',')
                - 1
             )
        INTO v_objectlist (v_no_of_objects)
        FROM DUAL;
      SELECT SUBSTR (
                v_copy_of_objectlist,
                  LENGTH (v_objectlist (v_no_of_objects))
                + 2
             )
        INTO v_copy_of_objectlist
        FROM DUAL;
      v_no_of_objects :=   v_no_of_objects
                         - 1;
   END LOOP;

   
/*Populating last item of array*/
   SELECT v_copy_of_objectlist
     INTO v_objectlist (v_no_of_objects)
     FROM DUAL;

   FOR outer_loop IN 1 .. v_counter
   LOOP
      FOR inner_loop IN   outer_loop
                        + 1 .. v_counter
      LOOP
         -- Create the query for execution
         v_sql_query :=    v_sql_query
                        || v_default_query
                        || v_objectlist (outer_loop)
                        || ''''
                        || ' INTERSECT '
                        || v_default_query
                        || v_objectlist (inner_loop)
                        || ''''
                        || ' UNION ';
      END LOOP;
   END LOOP;

   SELECT SUBSTR (v_sql_query, 1,   LENGTH (v_sql_query)
                                  - 7)
     INTO v_sql_query
     FROM DUAL;
   DBMS_OUTPUT.put_line (   'FINAL--->'
                         || v_sql_query);
END column_show;


Herein I am just displaying the final query.
I'd run this query on probably Oracle Reports.


Thanks to all,
Diku

[Updated on: Thu, 05 July 2007 01:09]

Report message to a moderator

Re: Finding Common Columns in a set of tables [message #249566 is a reply to message #249563] Thu, 05 July 2007 01:13 Go to previous messageGo to next message
Michel Cadot
Messages: 64117
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
select column_name
from dba_tab_columns
where (owner,table_name) in (<your list>)
group by column_name
having count(*) = <number of table in your list>
/

Regards
Michel
Re: Finding Common Columns in a set of tables [message #249572 is a reply to message #249566] Thu, 05 July 2007 01:30 Go to previous messageGo to next message
diku
Messages: 23
Registered: April 2007
Junior Member
Thanks Michel.

I broke my head all last night and presto, you gave a much more elegant and simple solution. Laughing

Just as a clarification, can I modify this to view data for multiple schema/user(s) ?

[Updated on: Thu, 05 July 2007 01:30]

Report message to a moderator

Re: Finding Common Columns in a set of tables [message #249588 is a reply to message #249572] Thu, 05 July 2007 02:14 Go to previous messageGo to next message
Michel Cadot
Messages: 64117
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The query already handles this: see the (owner,table_name).
You can build your IN list like (('ME','MYTABLE'),('YOU','YOURTABLE')...)

Regards
Michel
Re: Finding Common Columns in a set of tables [message #249594 is a reply to message #249588] Thu, 05 July 2007 02:26 Go to previous message
diku
Messages: 23
Registered: April 2007
Junior Member
Thanks all over Michel.
That effectively seals the issue. Cool
Previous Topic: password
Next Topic: How to download oracle table data in an Excel Sheet Using Java
Goto Forum:
  


Current Time: Tue Dec 06 04:08:12 CST 2016

Total time taken to generate the page: 0.12248 seconds