Warning: Going through ActiveRecord is likely not something you want to do in production unless you have a replica database that your script or your model can point at. See the second part for MySQL and PostgreSQL examples in SQL.
How do you enumerate your models?
For apps created under Rails 5 app or later, all of your ActiveRecord models should be descendants ApplicationRecord as well. If this is an option, you’ll be able to filter out some noise from descendants such as SchemaMigration, etc…
Using the descendants of ApplicationRecord you can map the statistics… You’ll need to catch errors on tables that might only be in production-like environments if you’re running locally:
counts = ApplicationRecord.descendants.map do |klass|
[klass.name, klass.table_name, klass.count]
rescue Mysql2::Error, ActiveRecord::StatementInvalid # if certain tables aren't present in certain environments
nil
end
=> []
Oops. I’m in development so… no eager loading of the models.
Rails.application.eager_load! # if in development
counts = ApplicationRecord.descendants.map do |klass|
[klass.name, klass.table_name, klass.count]
rescue Mysql2::Error, ActiveRecord::StatementInvalid # if certain tables aren't present in certain environments
nil
end
# lots of SELECT COUNT(*) logs if you're on :debug level logging
# followed by an array of name/table name/counts
You might also want to compact that result due to nils:
Rails.application.eager_load!
counts = ApplicationRecord.descendants.map do |klass|
[klass.name, klass.table_name, klass.count]
rescue Mysql2::Error, ActiveRecord::StatementInvalid # if certain tables aren't present in certain environments
nil
end
From there, you can export to CSV:
Rails.application.eager_load!
counts = ApplicationRecord.descendants.map do |klass|
[klass.name, klass.table_name, klass.count]
rescue Mysql2::Error, ActiveRecord::StatementInvalid # if certain tables aren't present in certain environments
nil
end
output_filename = "/tmp/counts.csv"
CSV.open(output_filename, "wt") do |csv|
csv << ['class name', 'table name', 'row count']
counts.compact.each do |row|
csv << row
end
end
What about from the database itself?
The below options are *significantly* faster than going through the Rails stack, so unless you need additional information from Rails, you should refine the below options instead (especially for production)
- MySQL:
select table_name, sum(table_rows) from information_schema.tables where table_schema = 'app_dbname_development' group by table_name;
- Postgres courtesy of StackOverflow
select table_schema,
table_name,
(xpath('/row/cnt/text()', xml_count))[1]::text::int as row_count
from (
select table_name, table_schema,
query_to_xml(format('select count(*) as cnt from %I.%I', table_schema, table_name), false, true, '') as xml_count
from information_schema.tables
where table_schema = 'public' --<< change here for the schema you want
) t order by 3 DESC