Pavan DBA's Blog

The DBA Knowledge Store

script to get row count of all tables in a schema

Posted by Pavan DBA on May 24, 2012


***************************************************
script to get row count of all tables in a schema
***************************************************
#!/bin/csh

            sqlplus -s <<EOF
username/password  
spool tablecount.log
select
      table_name,
      to_number(
        extractvalue(
          xmltype(dbms_xmlgen.getxml(‘select count(*) c from ‘||table_name))
          ,’/ROWSET/ROW/C’)
          )
          count
    from user_tables order by table_name;
spool off
        exit
EOF

Note: This script is written in C shell. Do necessary changes if you are using any other shell. Do test it before executing in prod env’s

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: