qsvxag
Last Updated: February 25, 2016
·
717
· burke
582037f79cc8b997eed3c2e55980d474

List tables referenced by a SQL query

This function takes a SQL query and returns a list of all the tables it references.

require 'set'

module QueryAnalyzer

  KEYWORD = /(?:JOIN|FROM|INTO|UPDATE)/i
  NAME = /`?(\w+)`?/
  MORE_TABLES = /(?:\s*,\s*`?(?:\w+)`?)/
  TABLE_MATCH = /#{KEYWORD}\s+#{NAME}(#{MORE_TABLES}*)/io

  def self.tables(sql)
    tables = Set.new
    sql.scan(TABLE_MATCH).each do |name, more_tables|
      tables << name
      next if more_tables.empty?
      more_tables.split(/\s*,\s*/).drop(1).each do |t|
        tables << t.tr('`', '')
      end
    end
    tables.to_a
  end

end
Say Thanks
Respond