Hello
I have a standard schema named ABC and 600 more schema's over there in my database.They all has same table name and column name as on standard schema. But in some tables number of columns varying. So I need to compare all schemas with my standard schemas column name. I create below script but it is generating output in infinite loop. Kindly help me I am not good in plsql.
SET SERVEROUTPUT ON
DECLARE
V_COLS VARCHAR2(20);
BEGIN
FOR CUR_CCD IN(SELECT DISTINCT TABLE_NAME,OWNER FROM ALL_TABLES
WHERE OWNER LIKE 'CCD_MAIN'
)
LOOP
FOR CUR_USR IN(SELECT DISTINCT TABLE_NAME, OWNER
FROM ALL_TABLES
AND OWNER NOT LIKE 'CCD_MAIN'
ORDER BY 2)
LOOP
FOR CUR_COL IN (SELECT DISTINCT COLUMN_NAME FROM
(SELECT DISTINCT COLUMN_NAME FROM
ALL_TAB_COLUMNS
WHERE TABLE_NAME = CUR_CCD.TABLE_NAME
AND OWNER=CUR_CCD.OWNER
MINUS
SELECT DISTINCT COLUMN_NAME FROM
ALL_TAB_COLUMNS
WHERE TABLE_NAME = CUR_CCD.TABLE_NAME
AND OWNER =CUR_USR.OWNER))
LOOP
DBMS_OUTPUT.PUT_LINE(CUR_USR.OWNER||' : '||CUR_CCD.TABLE_NAME||' :'||CUR_COL.COLUMN_NAME);
END LOOP;
END LOOP;
END LOOP;
END;
/
Thanks
Neha
[mod-edit: code tags added by bb; next time please add them yourself]
[Updated on: Sat, 15 December 2012 10:14] by Moderator
Report message to a moderator