Ruby on Rails script to check mysql zero dates

find_zero_dates.rb

#
# Check for every table with temporal columns if there are records that have dates set to '0000-00-00'

conn = ActiveRecord::Base.connection

# get a list of tables and columns with a date type excluding merge tables
query = "select concat(`table_schema`,'.',`table_name`) as table_name,`COLUMN_NAME` from information_schema.`columns` where data_type like '%date%' AND concat(`table_schema`,'.',`table_name`) not in (select concat(`table_schema`,'.',`table_name`) from information_schema.tables where engine='mrg_myisam');"
list = conn.select_all( query )

# transform the list a bit for later querying
tables = {}
list.each do |r|
  if tables.include?(r["table_name"])
    # table is already in the array, just push the column name
    tables[r["table_name"]].push(r["COLUMN_NAME"])
  else
    # table is not in the hash yet, add it with its column
    tables[r["table_name"]] = [r["COLUMN_NAME"]]
  end
end

# query for the zero dates
tables.each do |tn, columns|
  col = columns.map { |cn| "#{cn} <= '0000-01-01'" }.join(" OR ")
  q = "select count(*) as c from #{tn} where #{col};"

  # run query
  puts q
  count = conn.select_value(q)
  puts count
  if count > 0
    puts "#{tn} has #{count} zero date records"
  end
end