Oracle dynamic find number of rows and columns per table-CodePudding

I have the following query, which returns the number of rows per table in a schema.

Can this also be modified to RETURN the number of columns per table too?


CREATE table stats (
table_name VARCHAR2(128),
num_rows NUMBER,
num_cols NUMBER 
);
/

DECLARE
val integer;
BEGIN
for i in (SELECT table_name FROM all_tables WHERE owner = 'Schema')
LOOP
EXECUTE IMMEDIATE 'SELECT count(*) from ' || i.table_name INTO val;
INSERT INTO stats VALUES (i.table_name,val);
END LOOP;
END;
/

CodePudding user response：

You can use the ALL_TAB_COLS dictionary table:

DECLARE
  val integer;
BEGIN
  FOR i IN (
    SELECT table_name,
           COUNT(*) AS num_cols
    FROM   all_tab_cols
    WHERE  owner = 'Schema'
    GROUP BY table_name
  )
  LOOP
    EXECUTE IMMEDIATE 'SELECT count(*) from ' || i.table_name INTO val;
    INSERT INTO stats VALUES (i.table_name,val, i.num_cols);
  END LOOP;
END;
/

db<>fiddle here