Chun Pan (Billy) chunpan

A non-hyphenated American, with kindness, integrity, and appreciation of true diversity. Maker by trade, artist by heart.

chunpan / find_zero_dates.rb

Last active July 20, 2021 01:35 — forked from jdvkivu/find_zero_dates.rb

Ruby on Rails script to check mysql zero dates

	#
	# 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