How can i retrieve table names with rows count from specific database?

Question

How can i retrieve table names and count of rows for the specific database ?

Like my database name is xyz. XYZ database I want fetch all the table and count of records for the specific tables.

Query

SELECT status.real_name AS "real_name", status.friendly_name AS "friendly_name", table_rows AS "quant_of_rows", ROUND((data_length + index_length)/1024/1024,2) AS "total_size_mb" FROM information_schema.TABLES RIGHT JOIN table_status ON status.real_name = information_schema.TABLES.TABLE_NAME WHERE information_schema.TABLES.table_schema='database_name';

This query displays 4 columns.

1. original table name
2. friendly table name
3. records of table
4. size of table

I am getting problem with 3rd column. For InnoDB tables, the table_rows is only a rough estimate used in SQL optimization.

Instead of table_rows can i use something like this ?

(select count(*) from information_schema.TABLES.TABLE_NAME) AS "Quant_of_Rows"

What are the other ways to get table_row for the database?

Answer 1

    @temp_table = []
    ActiveRecord::Base.connection.tables.each do |table|
      count = ActiveRecord::Base.connection.execute("SELECT COUNT(*) as count FROM #{table}").fetch_hash['count']
      size = ActiveRecord::Base.connection.execute("SHOW TABLE STATUS LIKE '#{table}'").fetch_hash
      @temp_table <<  {:table_name => table,
          :records => count.to_i,
          :size_of_table => ((BigDecimal(size['Data_length']) + BigDecimal(size['Index_length']))/1024/1024).round(2)
        }
      end
    end

Answer 2

SHOW TABLE STATUS

and look "name" and "rows" columns

Answer 3

Something like this? Or am I missing the point?

-- pete