{"id":130,"date":"2007-10-15T18:54:59","date_gmt":"2007-10-16T02:54:59","guid":{"rendered":"http:\/\/raffy.ch\/blog\/2007\/10\/15\/database-query-analysis\/"},"modified":"2007-10-15T18:54:59","modified_gmt":"2007-10-16T02:54:59","slug":"database-query-analysis","status":"publish","type":"post","link":"https:\/\/raffy.ch\/blog\/2007\/10\/15\/database-query-analysis\/","title":{"rendered":"Database Query Analysis"},"content":{"rendered":"<p><img decoding=\"async\" src=\"http:\/\/raffy.ch\/blog\/wp-content\/uploads\/2007\/10\/icon.thumbnail.jpg\" alt=\"icon.jpg\" style=\"margin: 0pt 10px 10px 0pt; float: left\" border=\"0\" \/>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&#8217;s just too complicated for a regex. I was wondering whether there is a way to take a SQL query, for example:<\/p>\n<p><code>select * from a.table1 a, b.tabl2 b join c.table3 on b.id1=c.id2 where a.foo='bar'<\/code><\/p>\n<p>and extract all the table names: a.table1, b.table2, c.table3. Are there tools to do that? Remember, I don&#8217;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?<\/p>\n","protected":false},"excerpt":{"rendered":"<p>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 [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[6,5,2],"tags":[],"class_list":["post-130","post","type-post","status-publish","format-standard","hentry","category-log-analysis","category-unix-scripting","category-visualization"],"aioseo_notices":[],"_links":{"self":[{"href":"https:\/\/raffy.ch\/blog\/wp-json\/wp\/v2\/posts\/130","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/raffy.ch\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/raffy.ch\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/raffy.ch\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/raffy.ch\/blog\/wp-json\/wp\/v2\/comments?post=130"}],"version-history":[{"count":0,"href":"https:\/\/raffy.ch\/blog\/wp-json\/wp\/v2\/posts\/130\/revisions"}],"wp:attachment":[{"href":"https:\/\/raffy.ch\/blog\/wp-json\/wp\/v2\/media?parent=130"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/raffy.ch\/blog\/wp-json\/wp\/v2\/categories?post=130"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/raffy.ch\/blog\/wp-json\/wp\/v2\/tags?post=130"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}