CODEWITHSUNDEEP
sqloracle-database
emma
emma

Reputation: 45

How to query specific column name from multiple table from SQL

I want to query the column_name=object_no in the multiple tables ( up to 100 tables). I'm using Oracle SQL Developer.

To shows the tables that do not have column_name=object_no, I tried with the SQL query below. 

SELECT TABLE_NAME
FROM USER_TAB_COLUMNS
WHERE NOT COLUMN_NAME LIKE '%OBJECT_NO%'
ORDER BY COLUMN_ID;

However, it show the same table multiple times and mixed up with the table that also have COLUMN_ID='OBJECT_NO'.

Upvotes: 1

Views: 707

Answers (3)

Rahul Jain
Rahul Jain

Reputation: 1399

Try this:

SELECT 
    DISTINCT TABLE_NAME 
FROM 
    USER_TAB_COLUMNS
WHERE 
    TABLE_NAME NOT IN 
    (
        SELECT 
            TABLE_NAME
        FROM 
             USER_TAB_COLUMNS
        WHERE COLUMN_NAME LIKE '%OBJECT_NO%'
     )

Upvotes: 1

Pham X. Bach
Pham X. Bach

Reputation: 5432

Because user_tab_columns has table_name and column_name in each rows, so your select only omit the row with that columns, not other rows with other columns of that table.

One query you could use is:

SELECT DISTINCT table_name
FROM user_tab_columns c
WHERE NOT EXISTS (
    SELECT 1
    FROM user_tab_columns
    WHERE column_name LIKE '%OBJECT_NO%'
        AND table_name = c.table_name
) 
ORDER BY table_name;

Other way could work is:

SELECT DISTINCT table_name
FROM user_tab_columns 
WHERE table_name NOT IN (
    SELECT table_name
    FROM user_tab_columns
    WHERE column_name LIKE '%OBJECT_NO%'
) 
ORDER BY table_name;

Upvotes: 1

shrek
shrek

Reputation: 887

Try this - 

SET SERVEROUT ON;

BEGIN

FOR R IN (SELECT 'SELECT '||A.COLUMN_NAME||' FROM '||A.TABLE_NAME||' ' AS QRY FROM USER_TAB_COLUMNS A
WHERE COLUMN_NAME = 'OBJECT_NO')
LOOP
    EXECUTE IMMEDIATE USING R.QRY;
END LOOP;
/

Upvotes: 0

Related Questions