Dの小部屋

忘れやすい私のメモ帳

テーブルの状況情報取得SQL

テーブルを作った人から嘘をつかれ、エラー発生の原因究明に手こずることがあるため、テーブルの状態を全部表示するSQLを開発中。逐一で更新する予定

DEFINE T_NAME  = '###テーブル名###';
DEFINE T_OWNER = '###スキーマ名###';

SET PAGES 50000
SET LINESIZE 9999
SET ECHO OFF
SET VERIFY OFF
SET TRIMSPOOL ON
SET FEEDBACK OFF
SET LONG 2000000000
ALTER SESSION SET NLS_DATE_FORMAT='YYYY/MM/DD HH24:MI:SS';

-- 対象テーブル名 --
PROMPT
PROMPT
PROMPT [ TABLE NAME ]
SET HEADING OFF
SELECT  &T_NAME' FROM DUAL;
SET HEADING ON

-- テーブル構成(DESCと同じ) --
PROMPT
PROMPT
PROMPT [ TABLE DESC ]
COL DATA_TYPE FORMAT A20
SELECT
    COLUMN_NAME, 
    CASE NULLABLE WHEN 'N' THEN 'NOT NULL' ELSE NULL END "NULL?",
    DATA_TYPE|| '(' || CASE WHEN DATA_TYPE IN ('NUMBER', 'FLOAT') THEN DATA_PRECISION ELSE DATA_LENGTH END || ')' "DATA_TYPE",
    LAST_ANALYZED
FROM DBA_TAB_COLUMNS
WHERE TABLE_NAME = UPPER('&T_NAME') AND OWNER=UPPER('&T_OWNER')
ORDER BY COLUMN_ID;

-- テーブルスペース --
PROMPT
PROMPT
PROMPT [ TABLESPACE ]
SELECT TABLESPACE_NAME FROM DBA_TABLES WHERE TABLE_NAME = UPPER('&T_NAME') AND OWNER=UPPER('&T_OWNER');

-- テーブル作成SQL --
PROMPT
PROMPT
PROMPT [ TABLE DDL ]
SELECT DBMS_METADATA.GET_DDL('TABLE',UPPER('&T_NAME'),UPPER('&T_OWNER')) AS DDL FROM DUAL;

-- シノニム --
PROMPT
PROMPT
PROMPT [ SYNONYM ]
COL PUBLIC FOR A6
SELECT 
    SYNONYM_NAME,
    CASE OWNER WHEN'PUBLIC' THEN 'YES' ELSE 'NO' END "PUBLIC"
FROM DBA_SYNONYMS 
WHERE TABLE_NAME=UPPER('&T_NAME') AND TABLE_OWNER=UPPER('&T_OWNER');

-- トリガー ---
PROMPT
PROMPT
PROMPT [ TRIGGER ]
COL TRIGGERING_EVENT FOR A50
SELECT 
    TRIGGER_NAME,TRIGGER_TYPE,TRIGGERING_EVENT
FROM DBA_TRIGGERS
WHERE TABLE_NAME=UPPER('&T_NAME') AND TABLE_OWNER=UPPER('&T_OWNER');

-- 制約 --
PROMPT
PROMPT
PROMPT [ CONSTRAINTS ]
SELECT
    C.CONSTRAINT_NAME CONSTRAINT_NAME,
    C.CONSTRAINT_TYPE CONSTRAINT_TYPE,
    CC.POSITION       POSITION, 
    CC.COLUMN_NAME    COLUMN_NAME,
    C.STATUS          STATUS
FROM DBA_CONSTRAINTS C, DBA_CONS_COLUMNS CC
WHERE 
    C.TABLE_NAME      = CC.TABLE_NAME AND 
    C.OWNER           = CC.OWNER AND
    C.CONSTRAINT_NAME = CC.CONSTRAINT_NAME AND 
    C.TABLE_NAME      = UPPER('&T_NAME') AND 
    C.OWNER           = UPPER('&T_OWNER')
ORDER BY 1,2,3,4,5;

-- 索引 --
PROMPT
PROMPT
PROMPT [ CONSTRAINTS ]
SELECT 
    I.INDEX_NAME       INDEX_NAME,
    I.INDEX_TYPE       INDEX_TYPE,
    I.LAST_ANALYZED    LAST_ANALYZED,
    IC.COLUMN_POSITION COLUMN_POSITION,
    IC.COLUMN_NAME     COLUMN_NAME
FROM DBA_INDEXES I, DBA_IND_COLUMNS IC
WHERE 
    I.TABLE_NAME    = IC.TABLE_NAME AND
    I.TABLE_OWNER   = IC.TABLE_OWNER AND
    I.INDEX_NAME    = IC.INDEX_NAME AND
    I.TABLE_NAME    = UPPER('&T_NAME') AND 
    I.TABLE_OWNER   = UPPER('&T_OWNER')
ORDER BY 1,2,3,4,5;

-- READ ONLYか? --
PROMPT
PROMPT
PROMPT [ READ_ONLY ? ]
SELECT 
    TABLE_NAME,
    STATUS,
    READ_ONLY
FROM DBA_TABLES 
WHERE
    TABLE_NAME = UPPER('&T_NAME') AND 
    OWNER      = UPPER('&T_OWNER')
ORDER BY 1,2,3;

EXIT;