データベースの各種情報を取得したい!!

TODO

• 解決したコード情報を記載!!　※解説含めた補足情報も(差分書く!?)

スキーマの全テーブル/カラム情報を取得したい!!

getALL_TAB_COLUMNS.sql

SET PAGESIZE 2000;
SET LINESIZE 200;
SET LONGCHUNKSIZE 200;
SET LONG 10000;

SELECT
    TABLE_NAME ||','|| COLUMN_NAME ||','|| DATA_TYPE ||','|| DATA_LENGTH ||','|| DATA_PRECISION ||','|| DATA_SCALE
  FROM ALL_TAB_COLUMNS TC
  WHERE OWNER='SYSTEM'
  ORDER BY TABLE_NAME,COLUMN_ID;

【参考サイト情報】
　◆[ Oracle ] テーブル定義情報の取得 ( ALL_TAB_COLUMNS )
　　https://hensa40.cutegirl.jp/archives/1195
　◆【Oracle】テーブルのカラム情報をSQLで取得する方法
　　https://qiita.com/riekure/items/b54c8a21d77e5fe1776d
　◆【Oracle】テーブル定義情報を確認する方法（テーブル名やカラム名、データ型で検索）
　　https://notepad-blog.com/content/90/

スキーマの全テーブルのレコード数を取得したい!!

tryTableCount.sql

-- 案1
select table_name,
to_number(extractvalue(xmltype(dbms_xmlgen.getxml(
  'select count(*) X from '||table_name)),'/ROWSET/ROW/X')) count
from all_tables
where owner = 'schema_name' 

-- 案2
select owner, table_name,
  to_number(
    extractvalue(
      xmltype(dbms_xmlgen.getxml(
        'select count(*) c from ' || owner || '.' || table_name))
      ,'/ROWSET/ROW/C')) "count"
from all_tables
where owner in ('hoge','fufu')
order by owner, table_name; 

-- 案3
select table_name,
  to_number(extractvalue(xmltype(dbms_xmlgen.getxml(
    'select count(*) c from '||owner||'.'||table_name
      )),'/ROWSET/ROW/C')) as count
from all_tables
where owner = 'EDW'

これだとテーブルがありませんでエラーとなってしまうので...(失敗の原因はわかったので、後で追記)

tableCountNG.sql

SELECT
  table_name,
  TO_NUMBER(
    extractvalue(
      xmltype(
        dbms_xmlgen.getxml('SELECT COUNT(*) c FROM '||table_name))
      ,'/ROWSET/ROW/C')) count
FROM user_tables;

以下のPL/SQLを試してみる...

tableCount1.sql

puts 'The best way to log and share programmers knowledge.'
DECLARE
  CURSOR c1 IS SELECT table_name, CONSTRAINT_name FROM user_constraints WHERE index_name IS NOT NULL;
BEGIN
  FOR r1 IN c1 LOOP
    SELECT r1.table_name AS table_name ,COUNT(*) AS count FROM r1.table_name;
  END LOOP;
END;
/

tableCount2.sql

set echo off
set heading off
set pagesize 0
set linesize 10000
set trimout on
set trimspool on
set feedback off

set serveroutput on size 1000000

col spool_file_name new_value spool_file_name for a100
select lower(username)||'_table_count_'|| to_char(sysdate,'YYYYMMDDHH24MISS') || '.csv' spool_file_name from user_users;

spool &spool_file_name
declare
        cursor cu is select segment_name from user_segments where segment_type = 'TABLE';
        sql_stmt varchar2(200);
        cnt number(20);
begin
        for rec in cu loop
                sql_stmt := 'select count(ROWID) from '||rec.segment_name;
                execute immediate sql_stmt into cnt;
                dbms_output.put_line(rec.segment_name||','||cnt);
        end loop;
end;
/
spool off

exit