How to select all the tables from database where the tables are empty(no rows)
for example database has 100 tables and 10 table have no rows.
I would like to know those 10 table names
Printable View
How to select all the tables from database where the tables are empty(no rows)
for example database has 100 tables and 10 table have no rows.
I would like to know those 10 table names
Here is an Oracle pl/sql for printing tables with no records,
declare
/*Cursor to fetch all table names in the current schema*/
cursor table_csr is
select table_name from user_tables;
cnt number(10);
l_sql varchar2(250);
begin
for table_rec in table_csr loop
/*dynamic SQL which returns the row count*/
l_sql := 'select count(*) into :l_cnt from ' || table_rec.table_name;
execute immediate l_sql into cnt;
if cnt = 0 then
dbms_output.put_line(table_rec.table_name);
end if;
end loop;
end;
Try to use the following
SELECT TABLE_NAME, OWNER ,AVG_ROW_LEN FROM ALL_TABLES WHERE AVG_ROW_LEN=0
Thanks dude...I have learnt a new concept today. Thanks a lot:)
Thanks a lot to James and debasisdas.
debasisdas or James please help me how to write the cursor which have to pickup all the data from user_tables and it has to write in a file format CSV.