Results 1 to 2 of 2

Thread: Count of records in every table in the database

  1. #1

    Default Count of records in every table in the database

    I have an anonymous block I run in Oracle to get the number of records in every table in a schema to get output like this. I have tried a DB Query node, DB Execute node. I have run the top query and passed that into a filter node and created 3 commands (ex Select COUNT(*) from DON>TABLE1 , etc). That works if I only have one row. Fails on 2 or more rows.

    Any ideas would be appreciated.


    Output
    DON.TABLE1 977
    DON.TABLE2 3
    DON.TABLE3 256658



    DECLARE
    match_count integer;

    BEGIN
    FOR t IN (SELECT owner, table_name
    FROM all_tab_columns
    WHERE owner = 'DON'
    GROUP BY owner, table_name
    )
    LOOP
    BEGIN
    EXECUTE IMMEDIATE
    'SELECT COUNT(*) FROM '||t.owner || '.' || t.table_name
    INTO match_count;
    dbms_output.put_line( t.owner || '.' || t.table_name ||' '||match_count );

    END;
    END LOOP;
    END;

  2. #2
    Lavastorm Employee gmullin's Avatar
    Join Date
    May 2014
    Location
    Chicago
    Posts
    185

    Default

    You won't be able to execute 3 different commands in the same node and have them on the same output pin. If you're using DB Execute you could put your 3 commands in 3 fields going into the node and output each one on a different pin. Your DB Execute would have 3 output pins and would look like this:

    sqlSelect(1,query1)
    sqlSelect(2,query2)
    sqlSelect(3,query3)

    Another option could be to use a Do While node and write out the output for each iteration. This will probably work better if you want to count all the tables and you don't have a set number of commands. Above I'm presuming you know you're 3 commands for 3 tables.

Posting Permissions

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