Get Oracle all referenced key columns datatype and data length of particular table-CodePudding

Using the below query, I can get list of all the referenced tables, column names and I would like to get column datatype and data length as well. Please help me on the query how to get the datatype and length.

SELECT DISTINCT 
    C.TABLE_NAME CHILD_TABLENAME, C.COLUMN_NAME
FROM 
    (SELECT 
         A.CONSTRAINT_NAME,
         A.CONSTRAINT_TYPE,
         A.TABLE_NAME,
         B.COLUMN_NAME,
         A.R_CONSTRAINT_NAME,
         B.POSITION
     FROM 
         USER_CONSTRAINTS A, USER_CONS_COLUMNS B
     WHERE 
         B.CONSTRAINT_NAME = A.CONSTRAINT_NAME
         AND A.CONSTRAINT_TYPE = 'R') C,
    USER_CONS_COLUMNS D
WHERE 
    D.CONSTRAINT_NAME = C.R_CONSTRAINT_NAME
    AND D.TABLE_NAME = 'Member'
    AND D.POSITION = C.POSITION;

CodePudding user response：

with your_data as
(
SELECT DISTINCT C.TABLE_NAME CHILD_TABLENAME,C.COLUMN_NAME
FROM (SELECT A.CONSTRAINT_NAME,
A.CONSTRAINT_TYPE,
A.TABLE_NAME,
B.COLUMN_NAME,
A.R_CONSTRAINT_NAME,
B.POSITION
FROM USER_CONSTRAINTS A, USER_CONS_COLUMNS B
WHERE B.CONSTRAINT_NAME = A.CONSTRAINT_NAME
AND A.CONSTRAINT_TYPE = 'R') C,
USER_CONS_COLUMNS D
WHERE D.CONSTRAINT_NAME = C.R_CONSTRAINT_NAME
AND D.TABLE_NAME = 'DEPT'
AND D.POSITION = C.POSITION
)
select y.*, c.data_type
from your_data y, user_tab_columns c
where y.child_tablename = c.table_name
and y.column_name = c.column_name;

Add whatever columns you like from user_tab_columns

CodePudding user response：

I doubt your table name is really Member, usually they are all upper-case. You don't need any sub-query. Try this one:

SELECT con.TABLE_NAME, 
    con.CONSTRAINT_NAME, 
    con_cols.COLUMN_NAME, 
    cols.DATA_TYPE, 
    cols.DATA_LENGTH,
    ref_con.TABLE_NAME AS REF_TABLE_NAME, 
    ref_con.CONSTRAINT_NAME AS REF_CONSTRAINT_NAME, 
    ref_con_cols.COLUMN_NAME AS REF_COLUMN_NAME, 
    ref_cols.DATA_TYPE AS REF_DATA_TYPE, 
    ref_cols.DATA_LENGTH AS REF_DATA_LENGTH
FROM USER_CONSTRAINTS con 
    JOIN USER_CONS_COLUMNS con_cols ON con_cols.CONSTRAINT_NAME = con.CONSTRAINT_NAME
    JOIN USER_TAB_COLUMNS cols ON cols.TABLE_NAME = con_cols.TABLE_NAME AND cols.COLUMN_NAME = con_cols.COLUMN_NAME
    JOIN USER_CONSTRAINTS ref_con ON ref_con.CONSTRAINT_NAME = con.R_CONSTRAINT_NAME
    JOIN USER_CONS_COLUMNS ref_con_cols ON ref_con.CONSTRAINT_NAME = ref_con_cols.CONSTRAINT_NAME AND ref_con_cols.POSITION = con_cols.POSITION
    JOIN USER_TAB_COLUMNS ref_cols ON ref_cols.TABLE_NAME = ref_con_cols.TABLE_NAME AND ref_cols.COLUMN_NAME = ref_con_cols.COLUMN_NAME
WHERE con.CONSTRAINT_TYPE = 'R'
    AND con.TABLE_NAME = 'MEMBER';

or with CTE:

WITH c AS (
    SELECT con.TABLE_NAME, con.CONSTRAINT_NAME, 
        con_cols.COLUMN_NAME, cols.DATA_TYPE, cols.DATA_LENGTH,
        con.CONSTRAINT_TYPE, con.R_CONSTRAINT_NAME, con_cols.POSITION 
    FROM USER_CONSTRAINTS con 
        JOIN USER_CONS_COLUMNS con_cols ON con_cols.CONSTRAINT_NAME = con.CONSTRAINT_NAME
        JOIN USER_TAB_COLUMNS cols ON cols.TABLE_NAME = con_cols.TABLE_NAME AND cols.COLUMN_NAME = con_cols.COLUMN_NAME)
SELECT c.TABLE_NAME, 
    c.CONSTRAINT_NAME, 
    c.COLUMN_NAME, 
    c.DATA_TYPE, 
    c.DATA_LENGTH,
    ref_c.TABLE_NAME AS REF_TABLE_NAME, 
    ref_c.CONSTRAINT_NAME AS REF_CONSTRAINT_NAME, 
    ref_c.COLUMN_NAME AS REF_COLUMN_NAME, 
    ref_c.DATA_TYPE AS REF_DATA_TYPE, 
    ref_c.DATA_LENGTH AS REF_DATA_LENGTH
FROM c
    JOIN c ref_c ON c.R_CONSTRAINT_NAME = ref_c.CONSTRAINT_NAME AND c.POSITION = ref_c.POSITION
WHERE c.CONSTRAINT_TYPE = 'R'
    AND c.TABLE_NAME = 'MEMBER';