Detect unused tables and columns

detect_unused_tables_and_columns.rb

# Paste this a your production console to detect which tables are empty, which
# columns are empty and which columns contain a single value.
#
# Adapted from http://blog.ianenders.com/coding/2013/07/02/detecting-unused-db-fields-in-rails.html

connection = ActiveRecord::Base.connection
connection.tables.each do |t|
  puts "----> #{t}"
  count_query = "SELECT COUNT(1) as count FROM #{t}"
  count = connection.select_all(count_query, "Count").first['count']

  puts "TABLE UNUSED #{t}" if count.to_i == 0

  columns = connection.columns(t).collect(&:name).reject {|x| x == 'id' }
  columns.each do |column|
    puts "----> #{t}.#{column}"
    distinct_query = "SELECT DISTINCT \"#{column}\" AS val FROM #{t} LIMIT 2"
    values = connection.select_all(distinct_query, "Distinct Check")
    if values.count == 1
      if values.first['val'].nil?
        puts "COLUMN UNUSED #{t}.#{column}"
      else
        puts "COLUMN SINGLE VALUE #{t}.#{column} -- #{values.first['val']}"
      end
    end
  end
end