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'.
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;
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%'
)
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;
/
