Oracle Table detail info

Table detail info



set echo off term on
accept 2 prompt "Enter the name of the Table: "
accept 1 prompt "Enter table owner: "
set heading on newpage 0
ttitle 'Table Description - Space Definition'

btitle off
column Nline newline
set pagesize 54
set linesize 78
set heading off embedded off verify off

-- set term off

set embedded on heading on

column Ts format a20
column Ta format a20
column Clu format a20
column Pcf format 99999999999990
column Pcu format 99999999999990
column Int format 99,999,999,990
column Mat format 99,999,999,990
column Inx format 99,999,999,990
column Nxt format 99,999,999,990
column Mix format 99,999,999,990
column Max format 99,999,999,990
column Pci format 99999999999990
column Num format 99,999,999,990
column Blo format 99,999,999,990
column Emp format 99,999,999,990
column Avg format 99,999,999,990
column Cha format 99,999,999,990
column Rln format 99,999,999,990
column Hdg format a30 newline
set heading off
select 'Table Name' Hdg, Table_Name Ta,
'Tablespace Name' Hdg, Tablespace_Name Ts,
'Cluster Name' Hdg, Cluster_Name Clu,
'% Free' Hdg, Pct_Free Pcf,
'% Used' Hdg, Pct_Used Pcu,
'Ini Trans' Hdg, Ini_Trans Int,
'Max Trans' Hdg, Max_Trans Mat,
'Initial Extent (K)' Hdg, Initial_Extent/1024 Inx,
'Next Extent (K)' Hdg, Next_Extent/1024 Nxt,
'Min Extents' Hdg, Min_Extents Mix,
'Max Extents' Hdg, Max_Extents Max,
'% Increase' Hdg, Pct_Increase Pci,
'Number of Rows' Hdg, Num_Rows Num,
'Number of Blocks' Hdg, Blocks Blo,
'Number of Empty Blocks' Hdg, Empty_Blocks Emp,
'Average Space' Hdg, Avg_Space Avg,
'Chain Count' Hdg, Chain_Cnt Cha,
'Average Row Length' Hdg, Avg_Row_len Rln,
'Last Analyzed' Hdg, Last_Analyzed La
from DBA_TABLES
where Table_Name=UPPER('&&2')
and Owner=UPPER('&&1')
/
set heading on
set embedded off
column Cn format a30 heading 'Column Name'
column Fo format a15 heading 'Type'
column Nu format a8 heading 'Null'
-- column Nds format 99,999,999 heading 'No Distinct'
-- column Dfl format 99 heading 'Dflt Len'
column Dfv format a12 heading 'Default Val'
ttitle 'Table Description - Column Definition'
select Column_Name Cn,
Data_Type ||
DECODE(Data_Type,
'NUMBER',
'('||TO_CHAR(Data_Precision)||
DECODE(Data_Scale,0,'',','||
TO_CHAR(Data_Scale))||')',
'VARCHAR2',
'('||TO_CHAR(Data_Length)||')',
'CHAR',
'('||TO_CHAR(Data_Length)||')',
'DATE','',
'LONG','',
'Error') Fo,
DECODE(Nullable,'Y','','NOT NULL') Nu,
Data_Default Dfv
from DBA_TAB_COLUMNS
where Table_Name=UPPER('&&2')
and Owner=UPPER('&&1')
order by Column_ID
/

ttitle off
prompt
prompt TABLE CONSTRAINTS
prompt
set heading on
column Cn format a20 heading 'Primary Constraint'
column Cln format a20 heading 'Table.Column Name'
column Ct format a7 heading 'Type'
column St format a7 heading 'Status'
column Ro format a20 heading 'Ref Owner|Constraint Name'
column Se format a70 heading 'Criteria ' newline
break on Cn on St
set embedded on
prompt Primary Key
prompt
select CNS.Constraint_Name Cn,
CNS.Table_Name||'.'||CLS.Column_Name Cln,
INITCAP(CNS.Status) St
from DBA_CONSTRAINTS CNS,
DBA_CONS_COLUMNS CLS
where CNS.Table_Name=UPPER('&&2')
and CNS.Owner=UPPER('&&1')
and CNS.Constraint_Type='P'
and CNS.Constraint_Name=CLS.Constraint_Name
order by CLS.Position
/
prompt Unique Key
prompt
column Cn format a30 heading 'Unique Key'
select CNS.Constraint_Name Cn,
CNS.Table_Name||'.'||CLS.Column_Name Cln,
INITCAP(CNS.Status) St
from DBA_CONSTRAINTS CNS,
DBA_CONS_COLUMNS CLS
where CNS.Table_Name=UPPER('&&2')
and CNS.Owner=UPPER('&&1')
and CNS.Constraint_Type='U'
and CNS.Constraint_Name=CLS.Constraint_Name
order by CLS.Position
/
prompt Foreign Keys
prompt
column Cln format a18 heading 'Foreign Key' newline
column Clfn format a18 heading 'Parent Key'
column Cn format a40 heading 'Foreign Constraint'
break on Cn on St skip 1
select CNS.Constraint_Name Cn,
INITCAP(CNS.Status) St,
CLS.Table_Name||'.'||CLS.Column_Name Cln,
CLF.Owner||'.'||CLF.Table_Name||'.'||CLF.Column_Name Clfn
from DBA_CONSTRAINTS CNS,
DBA_CONS_COLUMNS CLF,
DBA_CONS_COLUMNS CLS
where CNS.Table_Name=UPPER('&&2')
and CNS.Owner=UPPER('&&1')
and CNS.Constraint_Type='R'
and CNS.Constraint_Name=CLS.Constraint_Name
and CLF.Constraint_Name = CNS.R_Constraint_Name
and CLF.Owner = CNS.Owner
and CLF.Position = CLS.Position
order by CNS.Constraint_Name, CLS.Position
/
prompt Check Constraints
prompt
column Cn format a40 heading 'Check Constraint'
column Se format a75 heading 'Criteria'
set arraysize 1
set long 32000
select Constraint_Name Cn,
INITCAP(Status) St,
Search_Condition Se
from DBA_CONSTRAINTS
where Table_Name=UPPER('&&2')
and Owner=UPPER('&&1')
and Constraint_Type='C'
/
prompt View Constraints
column Cn format a40 heading 'View Constraint'
select Constraint_Name Cn,
INITCAP(Status) St,
Search_Condition Se
from DBA_CONSTRAINTS
where Table_Name=UPPER('&&2')
and Owner=UPPER('&&1')
and Constraint_Type='V'
/
set arraysize 30
















SQL> @tab1
Enter the name of the Table: EMP
Enter table owner: SCOTT
Table Name EMP
Tablespace Name USERS
Cluster Name
% Free 10
% Used
Ini Trans 1
Max Trans 255
Initial Extent (K) 64
Next Extent (K) 1,024
Min Extents 1
Max Extents 2,147,483,645
% Increase
Number of Rows 14
Number of Blocks 4
Number of Empty Blocks 0
Average Space 0
Chain Count 0
Average Row Length 38
Last Analyzed 15-OCT-11


1 row selected.


Mon Nov 07 page 1
Table Description - Column Definition

Column Name Type Null Default Val
------------------------------ --------------- -------- ------------
EMPNO NUMBER(4) NOT NULL
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)

8 rows selected.


TABLE CONSTRAINTS

Primary Key

Primary Constraint
--------------------
Table.Column Name Status
-------------------- -------
SYS_C0022543
EMP.EMPNO Enabled


1 row selected.

Unique Key


no rows selected

Foreign Keys


no rows selected

Check Constraints


no rows selected

View Constraints

no rows selected