Question 254614 in PostgreSQL Question posted in
The official documentation can be found here.

How to list all table names in a schema along with row count in DB2 or PostgreSQL with only read access?

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.

Tags:

2

Answers


  1. 0

    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. 0

    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