10-165 全テーブルの、レコード数を数える

SQLパズル

全テーブルの、レコード数を出力する

出力結果
TABLE_NAME  COUNT
----------  -----
DEPT            4
EMP            14
BONUS           0
SALGRADE        5

こちらを参考にさせていただきました(英語)

SQL

select
   table_name,
   to_number(
     extractvalue(
       xmltype(
    dbms_xmlgen.getxml('select count(*) c from ' || table_name))
       ,'/ROWSET/ROW/C')) "count"
  from user_tables
 where table_name not Like 'BIN$%'
   and (iot_type != 'IOT_OVERFLOW' or iot_type is null)
order by table_name;

解説

動的にSQLを作成しない方法です。
Viewにしとくといいかもしれませんね。

動的にSQLを作成する方法は、下記となります。
(from句で使っているtabsは、USER_TABLESのシノニムです)

declare
    execSQL     varchar2(200);
    willOut     varchar2(200);
    RecordCount number(38);
begin
    for rec_Work in (select TABLE_NAME
                       from tabs
                      where TABLE_NAME not Like 'BIN$%'
                      order by TABLE_NAME) Loop

        execSQL := 'select count(*) from ' || rec_Work.TABLE_NAME;
        execute immediate execSQL into RecordCount;

        willOut := rec_Work.TABLE_NAME || 'の件数は';
        willOut := willOut || to_number(RecordCount) || '件';
        DBMS_Output.Put_Line(willOut);
    end Loop;
end;
/

■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
US-OTN count(*) for all tables
US-OTNのXMLの達人 MichaelSさんの方法 (11g以降)

col table_name for a20
col cnt for a20

select table_name, trim(column_value) cnt from user_tables
     , xmltable(('count(ora:view("' || table_name || '"))'))
order by table_name;

TABLE_NAME  CNT
----------  ---
BONUS       0
DEPT        4
EMP         14
SALGRADE    5

**********************************************************************
スカラーサブクエリ方式にアレンジしたもの

col cnt for 9999

select table_name,
(select to_number(column_value)
   from xmltable(('count(ora:view("' || table_name || '"))'))) as cnt
  from user_tables
order by table_name;

xmlcast関数を使用したもの

select table_name,
xmlcast (xmlquery ( ('count(ora:view("' || table_name || '"))')
         returning content) as int) as cnt
  from user_tables;

---

How do I store the counts of all tables(英語)

---