CODEWITHSUNDEEP
sqloracle-database
saurabhk
saurabhk

Reputation: 140

sql query to find table_name and count(table_name) in a schema

sql query to find table_name and count(table_name) in a schema:

Example:

I get the table_name from this query:

SELECT * FROM USER_TABLES

and count(table_name) from 

select count(*) from employee
select count(*) from dept
select count(*) from subjects

now i want to get the result like this:

table_name     count(table_name)
Employee        100
dept            21
subjects        56

Upvotes: 0

Views: 1639

Answers (2)

Sathish
Sathish

Reputation: 4487

Try like this

select
       table_name,
       to_number(
         extractvalue(
           xmltype(
             dbms_xmlgen.getxml('select count(*) c ' ||
                                ' from '||owner||'.'||table_name))
           ,'/ROWSET/ROW/C')) count
   from all_tables
  where table_name In ( 
  SELECT table_name from user_tables )

OR Using Join

select
       a.table_name,
       to_number(
         extractvalue(
           xmltype(
             dbms_xmlgen.getxml('select count(*) c ' ||
                                ' from '||owner||'.'||a.table_name))
           ,'/ROWSET/ROW/C')) count
   from all_tables a JOIN user_tables u ON
  a.table_name=u.table_name  AND a.owner = user

Upvotes: 2

Frank Schmitt
Frank Schmitt

Reputation: 30775

Looks like a typical use case for a simple UNION ALL:

select 
  'Employee' table_name,
  count(*) from employee 
union all
select 
  'dept' table_name,
  count(*) from dept 
union all
select 
  'subjects' table_name,
  count(*) from subjects

If you want to automate this, you can iterate over USER_TABLES:

declare
  l_cnt pls_integer;
begin
  for cur in (select table_name from user_tables order by table_name)
  loop
    execute immediate 'select count(*) from ' || cur.table_name into l_cnt;
    dbms_output.put_line(cur.table_name || ' ' || l_cnt);
  end loop;
end;

Instead of simply printing the result, you can also build a SQL statement dynamically and use that afterwards:

declare
  l_sql varchar2(4000);
begin
  for cur in (select table_name from user_tables order by table_name)
  loop
    l_sql := l_sql || ' select ' || cur.table_name || ' as table_name, count(*) as cnt from ' || cur.table_name || ' union all';
  end loop;
  -- remove trailing UNION ALL
  l_sql := regexp_replace(l_sql, 'union all$', '');
  dbms_output.put_line(l_sql);
end;

Upvotes: 1

Related Questions