Archive for the ‘oracle’ Category

h1

Extract and group SQL WHERE clauses

2008-07-23

I’m just going through an exercise at the moment to check that an Oracle DB is properly indexed, and since all the queries are done from stored procedures, with 100,000+ lines of source sprinkled over hundreds of files, I wanted something to quickly snarf all the WHERE clauses out, rather than go looking for them by eye.

So I just scribbled down a silly perl script, catted all the PL/SQL source through it, and got a plausible-looking list of WHERE clauses, ordered by decreasing frequency of occurrence in the code.

Hooray! Time for a beer.