All Forums Database
abhi_pn 14 posts Joined 02/14
12 Dec 2014
Row Counts of all Tables in a database

Hello All,
I faced one scenario where I couldn't find any solution.
Let's take an example. 100 Tables are there in a database. How to know the row counts for all the 100 tables in this database.
 
Regards,
Abhi

Tags:
Raja_KT 1246 posts Joined 07/09
12 Dec 2014

one way is :You can develop the dynamic sql to get the select dbname.tablename, count(*) From dbc.tables where tablekind =  't'  and databasename = 'yourdb..'
or you can think of creating a table or volatile table with tablename and rowcount field and insert the results into it, then do a select from it.
 

Raja K Thaw
My wiki: http://en.wikipedia.org/wiki/User:Kt_raj1
Street Children suffer not by their fault. We can help them if we want.

abhi_pn 14 posts Joined 02/14
14 Dec 2014

Hi Raja,
Thanks for the reply. But see, for a table in a particular database, there will be only single entry in the DBC.Tables. Hence Count(*) is not of much use. It wont give the record count of the table. Can you pls elaborate if you are trying to say something different.
Thanks
Abhi

Raja_KT 1246 posts Joined 07/09
15 Dec 2014

Try something like this:

 Select    

     'Select ' || '''' || trim(databasename) || '.' || trim(tablename) || ''',' ||

     ' Count(*) From ' || trim(databasename) || '.' ||

     trim (tablename) || ';' (Title '')

     From dbc.tables

     Where tablekind =  'T'

     and databasename = 'whatever_db_you have'

     ;

Raja K Thaw
My wiki: http://en.wikipedia.org/wiki/User:Kt_raj1
Street Children suffer not by their fault. We can help them if we want.

Rohan_Sawant 55 posts Joined 07/14
17 Dec 2014

Hi Abhijit,
I have modified Raja's query a little.

SELECT    

'SELECT CAST(' || '''' || TRIM(DATABASENAME) || '.' || TRIM(TABLENAME) || ''' AS VARCHAR(100)),' ||

' COUNT(*) FROM ' || TRIM(DATABASENAME) || '.' ||

TRIM (TABLENAME) || ' UNION ALL'

FROM DBC.TABLES

WHERE 

TABLEKIND =  'T'

AND DATABASENAME = 'MDSP_DATAMART_T';

 

After you run the above query take the output to your SQL query window and just remove the last UNION ALL and run the query. The difference between the above and Raja's query is just that in the above you will get the output in ONE Answersheet.

You must sign in to leave a comment.