CODEWITHSUNDEEP
oracle-databaseplsqloracle11g
cihadakt
cihadakt

Reputation: 3214

how to find empty tables from list of tables in PL/SQL?

I have a list of tables like table1, table2,....table35. I need to find empty ones. Is there any quicker way to do it? I googled some but all solutions are about for finding empty tables in schema. This time I have a list of table names and want to find empty tables. Any suggestions?

Upvotes: 3

Views: 887

Answers (3)

kara
kara

Reputation: 3455

Get all tables form DBA_OBJECTS and loop through:

DECLARE
    v_cnt          NUMBER;
    v_schemaname   VARCHAR(20) := 'myschema';
BEGIN
    FOR i IN (SELECT DISTINCT OWNER, OBJECT_NAME
                FROM DBA_OBJECTS
               WHERE OBJECT_TYPE = 'TABLE' AND OWNER = v_schemaname)
    LOOP
        BEGIN
            EXECUTE IMMEDIATE
                'select count(*) FROM ' || i.owner || '.' || i.object_name || ' WHERE rownum = 1'
                INTO v_cnt;

            IF v_cnt = 0
            THEN
                DBMS_OUTPUT.put_line (
                    i.owner || '.' || i.object_name || ' IS EMPTY');
            END IF;
        EXCEPTION
            WHEN OTHERS
            THEN
                DBMS_OUTPUT.put_line (
                    i.owner || '.' || i.object_name || ' ERROR: ' || SQLCODE);
        END;
    END LOOP;
END;

Upvotes: 1

Littlefoot
Littlefoot

Reputation: 142778

One option is to loop through all tables, along with dynamic SQL. Something like this:

SQL> DECLARE
  2     l_list   VARCHAR2 (50) := 'emp,dept';
  3     l_cnt    NUMBER;
  4  BEGIN
  5     FOR cur_t IN (WITH tabs AS (SELECT l_list FROM DUAL)
  6                       SELECT REGEXP_SUBSTR (l_list,
  7                                             '[^,]+',
  8                                             1,
  9                                             LEVEL)
 10                                 table_name
 11                         FROM tabs
 12                   CONNECT BY LEVEL <= REGEXP_COUNT (l_list, ',') + 1)
 13     LOOP
 14        EXECUTE IMMEDIATE 'select count(*) from ' || cur_t.table_name || ' where rownum = 1'
 15           INTO l_cnt;
 16
 17        DBMS_OUTPUT.put_line (
 18           cur_t.table_name || ' contains ' || l_cnt || ' row(s)');
 19     END LOOP;
 20  END;
 21  /
emp contains 14 row(s)
dept contains 4 row(s)

PL/SQL procedure successfully completed.

SQL>

You'd include a condition which displays tables whose L_CNT = 0.

Upvotes: 0

wolφi
wolφi

Reputation: 8361

The quickest way I know is to gather fresh statistics for all your tables:

BEGIN dbms_stats.gather_schema_stats(user); END;
/

and then find the empty tables in the data dictionary:

SELECT table_name 
  FROM user_tables 
 WHERE table_name LIKE 'TABLE%' 
   AND num_rows=0;

Upvotes: 0

Related Questions