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.
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.
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.
Try something like this:
I have modified Raja's query a little.