Oracle DBA and database consultancy services : tru-uk.com

Script to display all Details about a Table

SET SERVEROUTPUT ON
SET ECHO OFF
SET FEEDBACK OFF
SET PAGES 0
SET LINES 132

REM
REM || Title : show_tab_desc.sql
REM ||
REM || Purpose : Script to identify everything to do with a table.
REM ||
REM ||
REM || Release No : 1
REM ||
REM || Variables : None
REM ||
REM || Schema : system
REM ||
REM ||

PROMPT SHOW THE DATABASE AND USER THAT YOU ARE LOGGED ONTO.
PROMPT ========================================================

SELECT * FROM SYS.V_$DATABASE;

SHOW USER

PROMPT Show the Table Structure
PROMPT ========================

COLUMN POS FORMAT 999 heading "POS"
COLUMN PCT_FREE FORMAT A4 heading "Null"

SELECT column_name,
data_type,
data_length,
nullable,
column_id pos
FROM sys.dba_tab_columns
WHERE owner = UPPER ('&&owner')
AND table_name = UPPER ('&&table')
ORDER BY column_id;

PROMPT Show Physical Attributes
PROMPT ========================

SELECT pct_free,
pct_increase,
initial_extent,
next_extent,
max_extents,
num_rows,
avg_row_len
FROM sys.dba_tables
WHERE owner = UPPER ('&&owner')
AND table_name = UPPER ('&&table');

PROMPT Show the actual Maximum Size of a Row
PROMPT ==============================

SELECT SUM(DATA_LENGTH)
FROM SYS.DBA_TAB_COLUMNS
WHERE OWNER = upper('&&owner')
AND TABLE_NAME = upper('&&table');

PROMPT Show the Number of Physical EXTENTS that have been allocated Attributes
PROMPT ========================================================

COLUMN segment_name FORMAT A30 HEADING 'Table Name'
COLUMN counter FORMAT 9999 HEADING 'Number Of Extents Used'

SELECT segment_name,
COUNT (*) counter
FROM sys.dba_extents
WHERE owner = UPPER ('&&owner')
AND segment_name = UPPER ('&&table')
GROUP BY segment_name;

PROMPT Show the Physical SIZE IN BYTES of the TABLE
PROMPT =====================================

COLUMN tabsize FORMAT 999999999999 HEADING 'Table Size In Bytes'

SELECT segment_name,
SUM (bytes) tabsize
FROM sys.dba_extents
WHERE owner = UPPER ('&&owner')
AND segment_name = UPPER ('&&table')
GROUP BY segment_name;

PROMPT GET ALL THE INDEX DETAILS
PROMPT =========================

PROMPT Show all the indexes and their columns for this table
PROMPT =====================================================

COLUMN owner FORMAT A8 heading "Index|Owner"
COLUMN table_owner FORMAT A8 heading "Table|Owner"
COLUMN index_name FORMAT A30 heading "Index Name"
COLUMN column_name FORMAT A30 heading "Column Name"
COLUMN column_position FORMAT 9999 heading "Pos"

BREAK ON constraint_name SKIP 1

SELECT ind.owner,
ind.table_owner,
ind.index_name,
ind.uniqueness,
col.column_name,
col.column_position
FROM sys.dba_indexes ind,
sys.dba_ind_columns col
WHERE ind.table_name = UPPER ('&&table')
AND ind.table_owner = UPPER ('&&owner')
AND ind.table_name = col.table_name
AND ind.owner = col.index_owner
AND ind.table_owner = col.table_owner
AND ind.index_name = col.index_name;

PROMPT Display all the physical details of the Primary and Other
PROMPT Indexes for this table
PROMPT =========================================================

COLUMN owner FORMAT A8 HEADING "Index|Owner"
COLUMN table_owner FORMAT A8 HEADING "Table|Owner"
COLUMN index_name FORMAT A30 HEADING "Index Name"
COLUMN column_name FORMAT A30 HEADING "Column Name"
COLUMN column_position FORMAT 9999 HEADING "Pos"
COLUMN pct_free FORMAT 999 HEADING "%|Free"
COLUMN pct_increase FORMAT 999 HEADING "%|Incr"
COLUMN initial_extent FORMAT 999999999 HEADING "Init|Extent"
COLUMN next_extent FORMAT 999999999 HEADING "Next|Extent"
COLUMN max_extents FORMAT 999 HEADING "Max|Ext"

SELECT ind.owner,
ind.table_owner,
ind.index_name,
ind.uniqueness,
col.column_name,
col.column_position,
ind.pct_free,
ind.pct_increase,
ind.initial_extent,
ind.next_extent,
ind.max_extents
FROM dba_indexes ind,
dba_ind_columns col
WHERE ind.table_name = UPPER ('&&table')
AND ind.table_owner = UPPER ('&&owner')
AND ind.table_name = col.table_name
AND ind.owner = col.index_owner
AND ind.table_owner = col.table_owner
AND ind.index_name = col.index_name;

PROMPT GET ALL THE CONSTRAINT DETAILS
PROMPT ==============================

PROMPT Show the Non-Foreign Keys Constraints on this table
PROMPT ====================================================================

COLUMN owner FORMAT A9 HEADING "Owner"
COLUMN constraint_name FORMAT A30 HEADING "Constraint|Name"
COLUMN r_constraint_name FORMAT A30 HEADING "Referenced|Constraint|Name"
COLUMN delete_rule FORMAT A9 HEADING "Del|Rule"
COLUMN table_name FORMAT A18 HEADING "Table Name"
COLUMN column_name FORMAT A30 HEADING "Column Name"
COLUMN position FORMAT 9999 HEADING "Pos"

BREAK ON CONSTRAINT_NAME SKIP 1

SELECT col.owner,
col.constraint_name,
col.column_name,
col.position,
DECODE (con.constraint_type, 'P', 'primary', 'R', 'foreign', 'U', 'unique', 'C', 'check') "Type"
FROM dba_cons_columns col,
dba_constraints con
WHERE col.owner = UPPER ('&&owner')
AND col.table_name = UPPER ('&&table')
AND constraint_type <> 'R'
AND col.owner = con.owner
AND col.table_name = con.table_name
AND col.constraint_name = con.constraint_name
ORDER BY col.constraint_name,
col.position;

PROMPT Show the Foreign Keys on this table pointing at other tables Primary
PROMPT Key Fields for referential Integrity purposes.
PROMPT ====================================================================

SELECT con.owner,
con.table_name,
con.constraint_name,
con.r_constraint_name,
con.delete_rule,
col.column_name,
col.position,
con1.table_name "Ref Tab",
con1.constraint_name "Ref Const"
FROM dba_constraints con1,
dba_cons_columns col,
dba_constraints con
WHERE con.owner = UPPER ('&&owner')
AND con.table_name = UPPER ('&&table')
AND con.constraint_type = 'R'
AND col.owner = con.owner
AND col.table_name = con.table_name
AND col.constraint_name = con.constraint_name
AND con1.owner = con.owner
AND con1.constraint_name = con.r_constraint_name
AND con1.constraint_type IN ('P', 'U');

PROMPT Show the Foreign Keys pointing at this table via the recursive call
PROMPT to the Constraints table.
PROMPT ================================================================

SELECT con1.owner,
con1.table_name,
con1.constraint_name,
con1.delete_rule,
con1.status,
con.table_name,
con.constraint_name,
col.position,
col.column_name
FROM dba_constraints con,
dba_cons_columns col,
dba_constraints con1
WHERE con.owner = UPPER ('&&owner')
AND con.table_name = UPPER ('&&table')
AND ( (con.constraint_type = 'P')
OR (con.constraint_type = 'U'))
AND col.table_name = con1.table_name
AND col.constraint_name = con1.constraint_name
AND con1.owner = con.owner
AND con1.r_constraint_name = con.constraint_name
AND con1.constraint_type = 'R'
GROUP BY con1.owner,
con1.table_name,
con1.constraint_name,
con1.delete_rule,
con1.status,
con.table_name,
con.constraint_name,
col.position,
col.column_name;

PROMPT
PROMPT Show all the check Constraints
PROMPT ==========================================================

SET HEADING OFF

SELECT 'alter table ',
table_name,
' add constraint ',
constraint_name,
' check ( ',
search_condition,
' ); '
FROM dba_constraints
WHERE owner = UPPER ('&&owner')
AND table_name = UPPER ('&&table')
AND constraint_type = 'C';

PROMPT
PROMPT Show all the Triggers that have been created on this table
PROMPT ==========================================================

PROMPT add query to extract Trigger Body etcc WHEN CLAUSE here.

SET ARRAYSIZE 1
SET LONG 6000000

SELECT owner,
'CREATE OR REPLACE TRIGGER ',
trigger_name,
description,
trigger_body,
'/'
FROM dba_triggers
WHERE owner = UPPER ('&&owner')
AND table_name = UPPER ('&&table');

PROMPT
PROMPT Show all the GRANTS made on this table and it's columns.
PROMPT ========================================================

PROMPT On tables
PROMPT =========

SELECT 'GRANT ',
privilege,
' ON ',
table_name,
' TO ',
grantee,
';'
FROM dba_tab_privs
WHERE owner = UPPER ('&&owner')
AND table_name = UPPER ('&&table');

PROMPT On Columns
PROMPT ===========

SELECT 'GRANT ',
privilege,
' ( ',
column_name,
' ) ',
' ON ',
table_name,
' TO ',
grantee,
';'
FROM dba_col_privs
WHERE owner = UPPER ('&&owner')
AND table_name = UPPER ('&&table');

This script is provided for educational purposes only. The script has been tested and appears to work as intended. However, you should always test any script before relying on it. No responsibility will be accepted for Lost or damage that may occur from it's use.