MySQL – Finding Tables With Certain Columns

I’m working on munging some data on our main application and needed to build a set of tables that I need to look at. We have 140 tables in the app, and being lazy, I figured I could just write something to build the list for me.

 columns = %w(property_id town_id area_id subdivision_id county_id property_type_id status_id service_id)
 ActiveRecord::Base.connection.execute("show tables").each do |rs|
   table = rs.first
   columns.each do |col|          
     ActiveRecord::Base.connection.execute("show columns from `#{table}` like '#{col}';").each do |rs|
       puts "table: #{table} => #{rs.first}"
     end
   end
 end

The columns variable is an array of column names I’m looking for. In the outer loop, I get the full list of public tables. I then iterate over each table checking if the table contains a column I’m looking for. I threw the above code in a rake task to run. It only works for MySQL, but would be easy enough to create for PostgreSQL. Unfortunately, the ‘show columns from …’ query doesn’t take or arguments, but this is something I’ll only need to run once.

This entry was posted in mysql, ruby. Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *