a ver si te sirve este codigo; te obtiene:
1. Column Details
2. PRIMARY KEY
3. INDEXES
4. FOREIGN KEYS
5. CONSTRAINTS
6. ROWCOUNT
7. Other Tables That REFER to this Table
8. PARTITIONED COLUMNS
9. PARTITIONS
10. TRIGGERS
11. DEPENDANTS
Código PHP:
SET AUTOTRACE OFF
SET TIMING OFF
COLUMN COMMENTS FORMAT A50
COLUMN column_name FORMAT A35
COLUMN Data_Type FORMAT A15
COLUMN DATA_DEFAULT FORMAT A20
COLUMN "PK Column" FORMAT A35
COLUMN "FK Column" FORMAT A20
UNDEF Owner
ACCEPT Owner PROMPT 'Enter Owner :'
UNDEF Table_Name
ACCEPT Table_Name PROMPT 'Enter Table Name :'
SET HEADING OFF
PROMPT
PROMPT Comments for Table &Table_Name.
SELECT COMMENTS
FROM ALL_TAB_COMMENTS
WHERE TABLE_NAME = UPPER('&Table_Name.')
AND Owner = UPPER('&Owner.') ;
SET HEADING ON
PROMPT
PROMPT Column Details for Table &Table_Name.
SELECT
ROWNUM "Sr No", T.COLUMN_NAME , T.Data_Type , T.DATA_LENGTH,
DECODE(T.Nullable, 'N' , 'NOT NULL' , 'Y', ' ') NULLABLE , T.Data_Default , C.Comments
FROM
ALL_TAB_COLS T , All_Col_Comments C
WHERE
T.OWNER = C.OWNER
AND T.TABLE_NAME = C.TABLE_NAME
AND T.COLUMN_NAME = C.COLUMN_NAME
AND T.TABLE_NAME = UPPER('&Table_Name.')
AND T.Owner = UPPER('&Owner.') ;
PROMPT
PROMPT PRIMARY KEY for Table &Table_Name.
select COLUMN_NAME
FROM ALL_CONS_COLUMNS
WHERE TABLE_NAME = UPPER('&Table_Name.')
AND Owner = UPPER('&Owner.')
AND CONSTRAINT_NAME = ( SELECT CONSTRAINT_NAME
FROM ALL_CONSTRAINTS
WHERE TABLE_NAME = UPPER('&Table_Name.')
AND CONS! TRAINT_T YPE = 'P'
AND Owner = UPPER('&Owner.')
)
ORDER BY POSITION
/
PROMPT
PROMPT INDEXES for Table &Table_Name.
BREAK ON INDEX_NAME ON UNIQUENESS SKIP 1
SELECT I.INDEX_NAME , C.COLUMN_NAME , I.UNIQUENESS
FROM ALL_IND_COLUMNS C , ALL_INDEXES I
WHERE C.INDEX_NAME = I.INDEX_NAME
AND C.TABLE_NAME = I.TABLE_NAME
AND I.TABLE_NAME = UPPER('&Table_Name.')
AND I.Owner = UPPER('&Owner.')
AND C.Table_Owner = UPPER('&Owner.')
AND NOT EXISTS ( SELECT 'X'
FROM ALL_CONSTRAINTS
WHERE CONSTRAINT_NAME = I.INDEX_NAME
AND Owner = UPPER('&Owner.')
)
ORDER BY INDEX_NAME , COLUMN_POSITION
/
CLEAR BREAKS
PROMPT
PROMPT FOREIGN KEYS for Table &Table_Name.
BREAK ON CONSTRAINT_NAME ON TABLE_NAME ON R_CONSTRAINT_NAME SKIP 1
COLUMN POSITION NOPRINT
SELECT UNIQUE A.CONSTRAINT_NAME,
C.COLUMN_NAME "FK Column" ,
B.TABLE_NAME || '.' || B.COLUMN_NAME "PK Column",
A.R_CONSTRAINT_NAME ,
C.POSITION
FROM ALL_CONSTRAINTS A, ALL_CONS_COLUMNS B, ALL_CONS_COLUMNS C
WHERE A.R_CONSTRAINT_NAME=B.CONSTRAINT_NAME
AND B.OWNER=UPPER('&OWNER')
AND A.CONSTRAINT_NAME=C.CONSTRAINT_NAME
AND A.OWNER=C.OWNER
AND A.OWNER = B.OWNER
AND A.TABLE_NAME=C.TABLE_NAME
AND B.POSITION=C.POSITION
AND A.TABLE_NAME LIKE UPPER('&TABLE_NAME')
ORDER BY A.CONSTRAINT_NAME, C.POSITION
/
COLUMN POSITION NOPRINT
CLEAR BREAKS
PROMPT
PROMPT CONSTRAINTS for Table &Table_Name.
SELECT CONSTRAINT_NAME , SEARCH_CONDITION
FROM ALL_CONSTRAINTS
WHERE TABLE_NAME = UPPER('&Table_Name.')
AND Owner = UPPER('&Owner.')
AND CONSTRAINT_TYPE NOT IN ( 'P' , 'R');
PROMPT
PROMPT ROWCOUNT for Table &Table_Name.
SET FEEDBACK OFF
SET SERVEROUTPUT ON
DECLARE N NU MBER ;
V VARCHAR2(100) ;
BEGIN
V := 'SELECT COUNT(*) FROM ' || UPPER('&Table_Name.') ;
EXECUTE IMMEDIATE V INTO N ;
DBMS_OUTPUT.PUT_LINE (N);
END;
/
SET FEEDBACK ON
PROMPT
PROMPT Tables That REFER to Table &Table_Name.
BREAK ON TABLE_NAME ON CONSTRAINT_NAME skip 1
SELECT C.TABLE_NAME , C.CONSTRAINT_Name , CC.COLUMN_NAME "FK Column"
FROM ALL_CONSTRAINTS C
, All_Cons_colUMNs CC
WHERE C.Constraint_Name = CC.Constraint_Name
AND R_CONSTRAINT_NAME = ( SELECT CONSTRAINT_NAME
FROM ALL_CONSTRAINTS
WHERE TABLE_NAME = UPPER('&Table_Name.')
AND CONSTRAINT_TYPE = 'P'
AND Owner = UPPER('&Owner.')
)
AND C.Owner = UPPER('&Owner.')
/
CLEAR BREAKS
PROMPT
PROMPT PARTITIONED COLUMNS for Table &Table_Name.
SELECT COLUMN_NAME , COLUMN_POSITION
FROM All_Part_Key_Columns
WHERE NAME = UPPER('&Table_Name.')
AND Owner = UPPER('&Owner.') ;
PROMPT
PROMPT PARTITIONS for Table &Table_Name.
SELECT PARTITION_NAME , NUM_ROWS
FROM All_Tab_Partitions
WHERE TABLE_NAME = UPPER('&Table_Name.')
AND Table_Owner = UPPER('&Owner.') ;
PROMPT
PROMPT TRIGGERS for Table &Table_Name.
SELECT Trigger_Name
FROM All_Triggers
WHERE TABLE_NAME = UPPER('&Table_Name.')
AND Owner = UPPER('&Owner.') ;
PROMPT
PROMPT DEPENDANTS for Table &Table_Name.
BREAK ON TYPE SKIP 1
SELECT TYPE , NAME
FROM ALL_DEPENDENCIES
WHERE REFERENCED_NAME = UPPER('&Table_Name.')
ORDER BY TYPE ;
CLEAR BREAKS
SET TERMOUT OFF
SET AUTOTRACE ON
SET TIMING ON
SET TERMOUT ON