I need a query which will give the tablename and the count of the number of rows. Is it possible?

    Requires Free Membership to View

    Login

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.

This was first published in August 2007

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.
    Back to top