I need a query which will give the tablename and the count of the number of rows. Is it possible?
Yes, it's fairly straightforward.
select 'Customers' as tablename , count(*) as row_count from Customers union all select 'Orders' as tablename , count(*) as row_count from Orders union all select 'Products' as tablename , count(*) as row_count from Products union all ...
If you need to run this for all tables in your database, you can generate the list of tables from the INFORMATION_SCHEMA.TABLES view:
select table_name from information_schema.tables where table_schema = 'mydatabase'
Then you can use the result set of this query along with a text editor to generate the first query.