Row Counts of all Tables in a database

Database

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 (1)
5 REPLIES

Re: Row Counts of all Tables in a database

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.

Re: Row Counts of all Tables in a database

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

Re: Row Counts of all Tables in a database

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'

     ;

Re: Row Counts of all Tables in a database

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.

N/A

Re: Row Counts of all Tables in a database

Is there a way to execute the results without having to copy and paste them into a different window?