9 Replies Latest reply: Nov 19, 2013 1:33 PM by 969952 RSS

    SQL Query

    969952

      Hi All,

       

      I am working on Scott schema. I have around 1000 tables in Scott schema.

       

      I would like to know how many columns contains DEPTNO columns and would like to retrieve those tables list which are having DEPTNo columns having  DEPTNO=20

       

      Can any one please help me out.

       

      Thanks.

        • 1. Re: SQL Query
          969952

          query all the tables in the SCOTT schema  with the predicate of where DEPTNO = ‘BC20’ .. this is the requirement..

          • 2. Re: SQL Query
            JonWat

            select table_name,column_name

            from user_tables

            where column_name = 'DEPTNO'

             

            will get you the list of tables that you want.

             

            With that you need:

             

            select 'table_name' from dual

            where exists

                 (select *

                 from table_name

                 where deptno = 20

                 )

             

            for each table, probably UNIONed together. So you just use the metadata to write your query, then execute the query.

            • 3. Re: SQL Query
              969952

              not working..

              • 4. Re: SQL Query
                Frank Kulash

                Hi,

                 

                 

                 

                969952 wrote:

                 

                Hi All,

                 

                I am working on Scott schema. I have around 1000 tables in Scott schema.

                 

                I would like to know how many columns contains DEPTNO columns and would like to retrieve those tables list which are having DEPTNo columns having  DEPTNO=20

                 

                Can any one please help me out.

                 

                Thanks.

                You shouldn't create your own tables in any Oracle-supplied schema, such as SCOTT.  Create your own schemas for your own objects.  It will make things much simpler, especailly when uprdading the database.

                 

                Why do you have so many tables?

                The fact that you need to do the same thing on lots of different tables suggests that maybe they shouldn't be separate tables; maybe they should be combined into 1 table, with an additional column to identify which of the current tables each row belongs to.

                 

                Given that you need to work with the present table structure, you can query all_tab_columns to see which tables have a DEPTNO column.

                To search each one for deptno=20 requires dynamic SQL.

                • 5. Re: SQL Query
                  969952

                  Am just giving an exaple. I have create daround 84 tables in my schema.. Now i would like to query

                  all the tables in the PRODUCT schema  with the predicate of where PRODUCTNO = ‘B20’ her PRODUCT NUMBER IS VARCHAR2 data type.

                  • 6. Re: SQL Query
                    Martin Preiss

                    maybe you could use something like:

                    select owner

                         , table_name

                         , to_number(

                              extractvalue(

                                        xmltype(

                                                dbms_xmlgen.getxml('select count(*) c from '|| owner || '.'|| table_name || ' where deptno = 20')

                                                )

                                        ,'/ROWSET/ROW/C')) row_count

                      from dba_tab_cols

                    where column_name = 'DEPTNO'

                    order by owner, table_name;

                     

                    OWNER                          TABLE_NAME                      ROW_COUNT

                    ------------------------------ ------------------------------ ----------

                    SCOTT                          DEPT                                    1

                    SCOTT                          EMP                                     5

                    Since it contains xml operations I guess I saw it many years ago in Marco Gralike's blog. Of course the generated queries could take a lot of time.

                     

                    P.S.: adding the rownum predicate from Frank Kulash's PL/SQL solution would also be a good idea here.

                    • 7. Re: SQL Query
                      Frank Kulash

                      Hi,

                       

                       

                      969952 wrote:

                       

                      Am just giving an exaple. I have create daround 84 tables in my schema.. Now i would like to query

                      all the tables in the PRODUCT schema  with the predicate of where PRODUCTNO = ‘20’

                      Why give misleading examples?  Is it that much  harder to say "PRODUCT" than it is to say "SCOTT"?

                      Regardless of what schema(s) the tables are in, it still sounds like you should combine them into 1 table.

                       

                      Here's one way to do the dynamic SQL:

                       

                      SET SERVEROUTPUT ON

                       

                      DECLARE
                          num_found    PLS_INTEGER;
                          sql_txt      VARCHAR2 (100);
                      BEGIN
                          FOR  c  IN  (
                                          SELECT  owner
                                          ,       table_name
                                          FROM    all_tab_columns
                                          WHERE   owner        IN ('PRODUCT)
                                          AND     column_name  = 'PRODUCTNO'
                                      )
                          LOOP
                              sql_txt := 'SELECT COUNT (*)'
                                      || ' FROM ' || c.owner || '.' || c.table_name
                                      || ' WHERE productno = 20'
                                      || ' AND ROWNUM = 1';
                       
                      --      dbms_output.put_line (sql_txt || ' = sql_txt');   -- For debugging
                              EXECUTE IMMEDIATE  sql_txt 
                                  INTO  num_found;

                       

                              IF  num_found > 0
                              THEN
                                  dbms_output.put_line (c.table_name);
                              END IF;
                          END LOOP;
                      END;
                      /

                      • 8. Re: SQL Query
                        969952

                        Hi,

                         

                        Thanks for your qucik response. I am looking ofr ProdcutNO B20. Prodcutno is varchar2 data type. Please have a look.. I am not getting expected output..

                        • 9. Re: SQL Query
                          969952

                          hER EYOU CAN FIND THE REQUIREMENT.

                           

                          Can you pls query all the tables in the PRODUCT schema with the predicate of where PRODUCT_NO= ‘B20’