skip to Main Content

There are multiple solutions online but they all use ‘CREATE TABLE’ and ‘INSERT’ in their solutions.

I only have read access. So how to print all table names and row count in a schema? If possible how to print column count also, but it is optional.

2

Answers


  1. Answer for PostgreSQL.

    For exact counts, you need dynamic SQL. Create a function like:

    CREATE OR REPLACE FUNCTION f_tables_n_count_of(_schema text = 'public')
      RETURNS TABLE (table_name text, row_count bigint)
      LANGUAGE plpgsql AS
    $func$
    BEGIN
       FOR table_name IN
          SELECT c.relname
          FROM   pg_catalog.pg_class c
          WHERE  c.relnamespace = _schema::regnamespace
          AND    c.relkind = 'r'  -- only plain tables
          ORDER  BY 1
       LOOP
          RETURN QUERY EXECUTE format('SELECT %1$L, count(*) FROM %2$I.%1$I', table_name, _schema);
       END LOOP;
    END
    $func$;
    

    Call:

    SELECT * FROM f_tables_n_count_of();  -- defaults to 'public'
    

    Or:

    SELECT * FROM f_tables_n_count_of('myschema');
    

    Related:

    For very quick estimates:

    SELECT relname AS table_name
         , (CASE WHEN c.reltuples < 0 THEN NULL       -- never vacuumed
                 WHEN c.relpages = 0 THEN float8 '0'  -- empty table
                 ELSE c.reltuples / c.relpages END
         * (pg_catalog.pg_relation_size(c.oid)
          / pg_catalog.current_setting('block_size')::int))::bigint AS row_count
    FROM   pg_catalog.pg_class c
    WHERE  c.relnamespace = 'public'::regnamespace
    ORDER  BY 1;
    

    Detailed explanation:

    Login or Signup to reply.
  2. You need some tool which is able to interact with the DBMS_OUTPUT module module in Db2 for LUW.
    For examle, the following code works, if you run it with the Db2 CLP tool.

    --#SET TERMINATOR @
    
    SET SERVEROUTPUT ON@
    
    BEGIN
      DECLARE V_CNT BIGINT;
      
      FOR C1 AS 
        SELECT TABSCHEMA, TABNAME
        FROM SYSCAT.TABLES
        WHERE TYPE = 'T' AND TABSCHEMA = 'SYSIBM'
        --LIMIT 2
      DO 
        PREPARE S1 FROM 'SET ? = (SELECT COUNT (1) FROM "' || TABSCHEMA || '"."' || TABNAME || '")';
        EXECUTE S1 INTO V_CNT;
        CALL DBMS_OUTPUT.PUT_LINE (C1.TABNAME || ': ' || V_CNT);
      END FOR;
    END@
    
    SET SERVEROUTPUT OFF@
    

    But, if you use some other tool, then it depends on if this tool is able to work with INOUT parameters of Db2 stored procedures.
    For example, you can do something like this in DBeaver:

    CALL DBMS_OUTPUT.ENABLE ()@
    
    BEGIN
    ...
    END@
    
    -- Call it iteratively until you get 1 in the 2-nd parameter
    -- Since it can't call DBMS_OUTPUT.GET_LINES (?, :out) correctly.
    CALL DBMS_OUTPUT.GET_LINE (?, ?)@
    

    Look at the SYSCAT.COLUMNS system view for column count info.

    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search