-
Junior Member
Need a sql query or procedure or function
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
Last edited by jakilati; 08-07-2007 at 05:29 PM.
-
Expert Member
Re: Need a sql query or procedure or function
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;
-
Re: Need a sql query or procedure or function
Try to use the following
SELECT TABLE_NAME, OWNER ,AVG_ROW_LEN FROM ALL_TABLES WHERE AVG_ROW_LEN=0
-
Expert Member
Re: Need a sql query or procedure or function
Thanks dude...I have learnt a new concept today. Thanks a lot
-
Junior Member
Re: Need a sql query or procedure or function
Thanks a lot to James and debasisdas.
-
Junior Member
Re: Need a sql query or procedure or function
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.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules