Search for a string in all Columns and Tables (MYSQL)

dbsearch.rb

#!/usr/bin/env ruby

# Use this script to search for a string in all columns and tables in MYSQL
# INSTALLATION
#   gem install mysql2
#   gem install awesome_print
#   gem install colorize
# USAGE
#   ./dbsearch.rb 'find me in db'

require 'mysql2'
require 'awesome_print'
require 'colorize'
require 'optparse'

options = {}
OptionParser.new do |opts|
  opts.on("-t") do |t|
    options[:tables] = t
  end
end.parse!

# Set your general variables here
exclude_tables = %w(_pantheon_heartbeat *revision* *deleted* *cache*)
db_name     = 'db_name'
db_host     = 'localhost'
db_username = 'db_user'
db_password = 'db_pass'

search = ARGV[0]

abort("ERROR: MISSING SEARCH ARG    ---    dbsearch \"[search text]\"") if search.nil? && !options[:tables]

client = Mysql2::Client.new(host: db_host, username: db_username, password: db_password, database: db_name)

puts "Connected to database \"#{db_name}\"".green

table_data = []
tables = client.query("SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='#{db_name}'")
tables.each do |table|
  next_break = false

  exclude_tables.each do |exclude|
    matches = exclude.match(/\*([a-zA-Z]+)\*/i)
    next if matches.nil?
    if !table['TABLE_NAME'].match(/#{matches[1]}/i).nil?
      next_break = true
      break
    end
  end

  next if exclude_tables.include? table['TABLE_NAME']
  next if next_break

  table_indexes = client.query("SHOW INDEX FROM #{table['TABLE_NAME']} FROM #{db_name}")
  table_index = table_indexes.first
  table_index_column_name = table_index['Column_name'] if table_index

  column_data = []
  columns = client.query("SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA='#{db_name}' AND TABLE_NAME='#{table['TABLE_NAME']}'")

  columns.each do |column|
    column_data << column['COLUMN_NAME']
  end
  table_data << {
    name: table['TABLE_NAME'],
    columns: column_data,
    index: table_index_column_name,
  }
end

if options[:tables]
  # output all table names
  table_data.each do |table|
    puts "#{table[:name]}".red
    puts '  '+table[:columns].join(', ').blue
  end
  puts '------------------------------------------------------------------------------------------------'
  puts "Tables found: #{table_data.size}".green
else
  # do the search
  puts "Searching for: \"#{search}\"".green

  table_data.each do |table|
    column_query = table[:columns].collect do |column_name|
      "#{column_name} LIKE '%#{search}%'"
    end
    found_in_table = client.query("SELECT * FROM #{table[:name]} WHERE #{column_query.join(' OR ')}")
    next if found_in_table.size == 0
    puts '------------------------------------------------------------------------------------------------'
    puts "FOUND IN TABLE: #{table[:name]}".red

    table[:columns].each do |column_name|
      find_in_column = client.query("SELECT * FROM #{table[:name]} WHERE #{column_name} LIKE '%#{search}%'")
      next if find_in_column.size == 0
      puts "  #{find_in_column.size} TIME(S) IN COLUMN: #{column_name}".yellow
      puts "    SELECT * FROM #{table[:name]} WHERE #{column_name} LIKE '%#{search}%'".blue
    end
  end
end