Results 1 to 8 of 8
  1. #1
    Join Date
    Feb 2004
    Posts
    19

    Thumbs up Unanswered: SQL to list the count of records in 2 tables present in 2 different schemas

    There are 2 schemas with similar db structure.
    My requirement is to list down all the tables with count of records for both schemas.
    --------
    The output should be looking like this;

    Table1 - Count(in Schema1) - Count(in Schema2)
    Table2 - Count(in Schema1) - Count(in Schema2)
    ....
    --------
    I need to write a single SQL statement to get this data in a single row for each table.
    Could anyone help me?

    Thanks in Advance,
    AI

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    A single SQL statement? No. A single PL/SQL program, yes. You need a database link from one database to the other, then you can do something like this:
    Code:
    DECLARE
      v_count1 INTEGER;
      v_count2 INTEGER;
    BEGIN
      FOR r_tab IN (SELECT table_name FROM user_tables)
      LOOP
        EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM '||r_tab.table_name INTO v_count1;
        EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM '||r_tab.table_name || '@otherdb' INTO v_count2;
        DBMS_OUTPUT.PUT_LINE (RPAD(r_tab.table_name,31) || TO_CHAR(v_count1,'999999999') || ' ' || TO_CHAR(v_count2,'999999999') );
      END LOOP;
    END;
    /

  3. #3
    Join Date
    Apr 2004
    Location
    Toronto, Canada
    Posts
    249
    One way to do this would be by using a view and a user defined function.


    The view would list all the tables that you are interested in. Here you might choose to use the data dictionary.

    The user defined function would take the table name and the schema name as parameters, query the DBMS and return the count of rows.

    Thus, your SQL would look like:

    SELECT table_name,
    count_rows(table_name, 'Schema1') AS rows_in_schema1
    count_rows(table_name, 'Schema2') AS rows_in_schema2
    FROM system_catalog

    The function count_rows would use dynamic sql.

    Alternatively, you could analyze all your tables in the schemas of interest, then use the following sql:

    SELECT a.table_name, a.num_rows, b.num_rows
    FROM all_tables a, all_tables b
    WHERE a.table_name = b.table_name
    AND a.owner = 'SCHEMA1' and b.owner = 'Schema2';

    This query may perform poorly because of the self join on all_tables.
    Also, if a table exists in one schema and not the other, you will not see it.
    Furthermore, it assumes that the tables have been analyzed and are up to date.

    Hope that helps.

  4. #4
    Join Date
    Feb 2004
    Posts
    19

    Smile

    Thank You, guys.
    Your solutions helped me.

    -AI

  5. #5
    Join Date
    Jul 2011
    Posts
    2

    SQL to list the count of records in 2 different tables present in 2 different schemas

    I need PLSQL to display count of rows of specific tables in different schemas. I have spent hours trying but arrive at nothing. Anonymous block or stored procedure.

    Any help will be appreciated.

  6. #6
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    post SQL that returns schema names & table names that are to be counted
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  7. #7
    Join Date
    Jul 2011
    Posts
    2
    I am very new to PLSQL and here is what I came up with

    declare
    cnt int;
    schema_name varchar2 ;
    table_name varchar2;
    begin
    select count (*) into cnt
    from &schema_name..&table_name;
    dbms_output.put_line ('Table count is : ' || cnt);
    end;
    /


    I need to have parameter accept schema name and table and return the count of the table. So sorry for the stupidity on my part. THanks for any assistance you can render.

  8. #8
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •