How to retrieve all the columns from all of the tables in a particular database which are not part of primary keys and foreign keys

Question

I am really new to Stackoverflow apologies for any mistakes.

I am working on Qlikview. It doesn't allow the columns which are of same name in two different tables. I used to create aliases for each and every column every time when i need to import tables from oracle. Now i want to deal with large database. So i decided to create a procedure that takes all the non constraint column as input and append there table name with column names.

I have written a sql query with basic knowledge that returns now primary columns but when coming to foreign keys doesn't work (it retrieving the columns which are having foreign keys)

My query is as follows

SELECT C.table_name,
        C.column_name
FROM user_constraints a,
  user_cons_columns b,
  ALL_TAB_COLUMNS C
WHERE a.OWNER          =b.owner
AND a.OWNER            =C.owner
AND C.COLUMN_NAME      !=b.COLUMN_NAME
AND a.CONSTRAINT_NAME  =b.CONSTRAINT_NAME
AND a.table_name       =b.table_name
AND a.table_name       =C.table_name
AND a.constraint_type IN('P','R')
AND a.table_name NOT LIKE 'BIN%'
AND A.TABLE_NAME NOT LIKE 'DEF%'
AND b.table_name NOT LIKE 'BIN%'
AND b.TABLE_NAME NOT LIKE 'DEF%'
AND C.table_name NOT LIKE 'BIN%'
AND C.TABLE_NAME NOT LIKE 'DEF%';

Any suggestions will be appreciated

Thank you

score 2 · Accepted Answer · answered Nov 20 '15 at 12:10

This should give you all columns in all tables for a given schema_name where those columns are not part of a primary or foreign key

SELECT  atc.owner,
    atc.table_name,
    atc.column_name 
FROM
    all_tab_columns atc
WHERE
    NOT EXISTS
    (
        SELECT  acc.owner,
            acc.table_name,
            acc.column_name
        FROM
            all_cons_columns acc
        LEFT
        JOIN    all_constraints ac ON acc.owner = ac.owner AND ac.constraint_name = acc.constraint_name AND ac.constraint_type IN ('P', 'R')
        WHERE
            atc.owner = atc.owner
        AND acc.table_name = atc.table_name
        AND acc.column_name = atc.column_name
    )
AND atc.owner = 'YOUR_SCHEMA_NAME'
ORDER
BY  1, 2
/

How to retrieve all the columns from all of the tables in a particular database which are not part of primary keys and foreign keys

1 Answers1