Database Query Analysis

img src="http://raffy.ch/blog/wp-content/uploads/2007/10/icon.thumbnail.jpg" border="0" alt="icon.jpg" />

I was playing with database audit logs for a bit to try and visualize some aspects of them. While doing so, I came across a pretty interesting problem.

The audit logs contain entries that indicate what exact SQL query was executed. Now, I am not interested in the entire query, but I need to know which tables were touched.

I was trying to build some regular expressions to extract that information from the query, but I gave up pretty quickly.

It’s just too complicated for a regex. I was wondering whether there is a way to take a SQL query, for example:

select * from a.table1 a, b.tabl2 b join c.table3 on b.id1=c.id2 where a.foo='bar'

and extract all the table names: a.table1, b.table2, c.table3. Are there tools to do that? Remember, I don’t have the database with these tables.

I only have a log from some database. The script should support all the SQL perks like joins, nested selects, etc. Anyone have a good way to do this?