Aunque existe la instrucción DESCRIBE, en ocasiones es útil, obtener metadatos de una BD de oracle, ej. para temas de autogeneración de código.
-- Seleccionar llave primaria:
SELECT
cols.column_name
FROM all_constraints cons, all_cons_columns cols
WHERE cols.table_name = ‘TABLA’
AND cons.constraint_type = 'P'
AND cons.constraint_name = cols.constraint_name
AND cons.owner = cols.owner
ORDER BY cols.table_name, cols.position;
-- Seleccionar relaciones Padre
SELECT DISTINCT
a.table_name AS TableName,
b.column_name AS ColName,
c.table_name AS ParentTable,
d.column_name AS ParentCol
FROM
all_constraints a,
all_cons_columns b,
all_constraints c,
all_cons_columns d
WHERE a.constraint_name = b.constraint_name
AND b.table_name = 'TABLA'
AND a.r_constraint_name = c.constraint_name
AND c.constraint_name = d.constraint_name
AND a.r_constraint_name is not null
AND a.status = 'ENABLED'
-- Seleccionar relaciones Hijo
SELECT
a.owner||'.'||a.table_name parent_table,
b.column_name,
c.owner||'.'||c.table_name child_table,
d.column_name child_pk
FROM
all_constraints a,
all_cons_columns b,
all_constraints c,
all_cons_columns d
WHERE
a.constraint_name = b.constraint_name
AND a.constraint_name = c.r_constraint_name
AND c.constraint_name = d.constraint_name
AND a.table_name = upper('TABLE')
ORDER BY child_table
-- Seleccionar campos de una Tabla
SELECT
column_name
FROM all_tab_columns
WHERE owner = v_Owner
AND table_name = 'TABLE'
ORDER BY column_id;
