Pages

Recently Viewed

Wednesday, February 1, 2023

SQL Script to find list of tables updated today

DECLARE 
    sql_st VARCHAR2(1000);
    no_of_records NUMBER;
BEGIN 
    dbms_output.put_line ('Table|No of Records Changed');
    dbms_output.put_line ('------|----------------------------------');
    FOR C1 in (SELECT table_name 
FROM all_tables a 
WHERE  table_name not lIKE '%$%' 
AND table_name not lIKE '%_A' 
AND exists (SELECT 1 
FROM all_tab_columns b 
WHERE a.table_name = b.table_name 
AND b.column_name = 'LAST_UPDATE_DATE'
)
order by table_name) 
    LOOP 
    no_of_records := 0;
    sql_st := 'Select count(1) from '|| c1.table_name ||' where last_update_date > TRUNC(SYSDATE)';
    BEGIN
            EXECUTE IMMEDIATE sql_st INTO no_of_records;
            dbms_output.put_line (c1.table_name||'|'||no_of_records);
    EXCEPTION 
        WHEN OTHERS THEN 
        dbms_output.put_line (c1.table_name ||'|'|| SQLERRM);
    END;
END Loop;
EXCEPTION 
WHEN OTHERS THEN 
    dbms_output.put_line ('Exception : '||SQLERRM);
END;

No comments:

Post a Comment