1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38
|
SELECT a.OWNER,
a.TABLE_NAME,
a.COLUMN_NAME,
a.DATA_TYPE,
a.DATA_LENGTH,
'('||a.DATA_PRECISION ||','|| a.DATA_SCALE||')' DATA_PRECISION,
DECODE (a.NULLABLE, 'N', 'NOT NULL',NULL),
c.CONSTRAINT_NAME,
DECODE (d.CONSTRAINT_TYPE, 'R', 'FOREIGN KEY',
'P', 'PRIMARY KEY',
NULL),
dr.CONSTRAINT_NAME REFERENCES_CONSTRAINT,
dr.OWNER REF_OWNER,
dr.table_name REFERENCES_TAB,
cr.COLUMN_NAME REFERENCES_COLUMN
FROM all_tab_columns a
LEFT JOIN all_cons_columns c
ON a.TABLE_NAME = c.TABLE_NAME
AND a.OWNER = c.OWNER
AND a.COLUMN_NAME = c.COLUMN_NAME
AND c.POSITION is not null
LEFT JOIN all_constraints d
ON a.TABLE_NAME = d.TABLE_NAME
AND a.OWNER = d.OWNER
AND c.CONSTRAINT_NAME = d.CONSTRAINT_NAME
AND d.CONSTRAINT_TYPE != 'C'
LEFT JOIN all_constraints dr
ON d.r_owner = dr.owner
AND d.r_constraint_name = dr.constraint_name
AND d.CONSTRAINT_TYPE != 'C'
LEFT JOIN all_cons_columns cr
ON dr.TABLE_NAME = cr.TABLE_NAME
AND a.OWNER = cr.OWNER
AND dr.CONSTRAINT_NAME = cr.CONSTRAINT_NAME
AND cr.POSITION is not null
WHERE a.table_name = 'TA_TABLE'
AND a.OWNER = 'TON_OWNER'; |
Partager