CODEWITHSUNDEEP
sqloracle-database
Sunny
Sunny

Reputation: 47

Print counts from multiple tables in one SQL

DB version: 11.1.2.3.0

I am using the below SQL:

SELECT tab_nam, cnt  
FROM  
(  
SELECT 'Tab1' "tab_nam", COUNT(1) "cnt" FROM Tab1  
UNION  
SELECT 'Tab2' "tab_nam", COUNT(1) "cnt" FROM Tab2  
);  

TAB_NAM   CNT  
-------   ------  
Tab1      23  
Tab2      10  

CREATE TABLE Tab1 (id NUMBER, name VARCHAR2(100));
CREATE TABLE Tab2 (id NUMBER, name VARCHAR2(100));

Is there any other way to get the output in the above form? I would like to get the resultset in above format only and not horizontally..

Reason for different SQL: I am not able to print the SQL output to HTML. Format used:

PRO <h3>Table Count</h3>
PRO <table>
PRO <tr>
PRO <th>Table Name</th>
PRO <th>Count</th>
PRO </tr>
SELECT '<tr>'||
'<td class="left">'||"tab_name"||'</td>'||
'<td class="center">'||"cnt"||'</td>'||
'</tr>'
FROM  (
SELECT 'Tab1' "tab_name", COUNT(1) "cnt" FROM Tab1
UNION
 SELECT 'Tab2' "tab_name", COUNT(1) "cnt" FROM Tab2);
PRO </table>

Tia..

Upvotes: 1

Views: 1838

Answers (1)

Lalit Kumar B
Lalit Kumar B

Reputation: 49082

There are several ways of finding the count of SOME/ALL tables in a single query. For example, let's say I want to get the count of rows from all tables in the schema 'SCOTT'.

1.Using SQL

SQL> column table_name format A15;
SQL> SELECT table_name,
  2    to_number(extractvalue(xmltype(dbms_xmlgen.getxml('select count(*) c from '
  3    ||owner
  4    ||'.'
  5    ||table_name)),'/ROWSET/ROW/C')) AS COUNT
  6  FROM all_tables
  7  WHERE owner = 'SCOTT'
  8  /

TABLE_NAME           COUNT
--------------- ----------
DEPT                     4
EMP                     14
BONUS                    0
SALGRADE                 5

SQL>

2.With 100% statistics gathered

SQL> EXEC DBMS_STATS.gather_schema_stats('SCOTT', estimate_percent => 100);

PL/SQL procedure successfully completed.

SQL> column owner format A10;
SQL> select owner, table_name, num_rows, sample_size, last_analyzed from all_tables where owner = 'SCOTT';

OWNER      TABLE_NAME        NUM_ROWS SAMPLE_SIZE LAST_ANAL
---------- --------------- ---------- ----------- ---------
SCOTT      DEPT                     4           4 12-OCT-14
SCOTT      EMP                     14          14 12-OCT-14
SCOTT      BONUS                    0           0 12-OCT-14
SCOTT      SALGRADE                 5           5 12-OCT-14

SQL>

3.Using PL/SQL

SQL> SET serveroutput ON;
SQL> DECLARE
  2    var_count NUMBER;
  3  BEGIN
  4    FOR i IN
  5    (SELECT table_name, owner FROM all_tables WHERE owner = 'SCOTT'
  6    )
  7    LOOP
  8      EXECUTE IMMEDIATE 'select count(*) from ' || i.table_name INTO var_count;
  9      dbms_output.put_line(i.table_name ||' - '|| var_count);
 10    END LOOP;
 11  END;
 12  /
DEPT - 4
EMP - 14
BONUS - 0
SALGRADE - 5

PL/SQL procedure successfully completed.

SQL>

But the SQL approach is simpler, isn't it?

Upvotes: 3

Related Questions