table_info.rb

#!/usr/bin/ruby
require 'rubygems'
require 'mysql'
require 'optparse'
options = {
  :database => '',
  :host=> 'localhost',
  :create=> false,
  :count=> false,
  :index=> false,
  :port=> 3306,
  :user=> 'root',
  :password => '',
}
begin
  OptionParser.new do |opt|
    opt.banner = "Usage: #{File.basename($0)} [Option]"
    opt.on("-d","--database String","database name") { |o|
      options[:database] = o
    }
    opt.on("-H","--hostname String","hostname. default: localhost") {|o|
      options[:host] = o
    }
    opt.on("-n","--number","count record number. caution! don't use ovarload") {
      options[:count] = true
    }
    opt.on("-c","--create","show create table list.") {
      options[:create] = true
    }
    opt.on("-i","--index","show index from table list.") {
      options[:index] = true
    }
    opt.on("-P","--port Integer","port. default: 3306.") {|o|
      options[:port] = o
    }
    opt.on("-u","--user String","user name. default:root"){|o|
      options[:user] = o
    }
    opt.on("-p","--password String","password. default:''"){|o|
      options[:password] = o
    }
    opt.on("-h","--help","print this message and quit") {
      puts opt.help
      exit 0
    }
    opt.parse!(ARGV)
  end
rescue OptionParser::ParseError => err
  $stderr.puts(err.message)
  exit 1
end
if options[:database] == nil or options[:database] == ""
  $stderr.puts("no database name.")
  exit 1
end
Mysql.init()
#接続開始
db = Mysql::new(options[:host], options[:user], options[:password],options[:database],options[:port])
#SQL文の実行
st = db.query("show tables")
st.each do |tbl|
  puts "TABLE=#{tbl[0]}"
  if options[:create]
    st2 = db.query("show create table #{tbl[0]}")
    st2.each do |attr|
      printf "%s\n"%attr[1]
    end
  end
  if options[:index]
    st3 = db.query("show index from #{tbl[0]}")
    key_name = {}
    st3.each do |idx|
      unless key_name[idx[2]]
        key_name[idx[2]] = {}
        key_name[idx[2]]["Cardinality"] = idx[6]
        if idx[1] == 1
          key_name[idx[2]]["Non_unique"] = "True"
        else
          key_name[idx[2]]["Non_unique"] = "False"
        end
        key_name[idx[2]]["Index_type"] = idx[10]
        key_name[idx[2]]["Column_name"] = []
      end
      key_name[idx[2]]["Column_name"].push idx[4]
    end
    next if key_name.size == 0
    key_data = []
    key_name.each do |key,val|
      if key == "PRIMARY"
        val["Key_name"] = key
        key_data.unshift val
      else
        val["Key_name"] = key
        key_data.push val
      end
    end
    key_data.each do |data|
      puts "key_name=>#{data['Key_name']}  Non_unique=>#{data['Non_unique']} "+
        "Cardinality=>#{data['Cardinality']} "+
        "Index_type=>#{data['Index_type']}  Column_name=>#{data['Column_name'].join(',')}"
    end
  end
  if options[:count]
    st4 = db.query("select count(*) from #{tbl[0]}")
    st4.each do |attr|
      printf "total count=%d\n"%attr[0]
    end
  end
  printf "\n\n"
end
#接続を切断する
db.close