jdbc_grep.py

from java.lang import *
from java.sql import *

# PARAMETERS YOU SHOULD SET ( search for '_here' to see ones u HAVE to set)
driver = 'com.mysql.jdbc.Driver'
url = 'jdbc:mysql://localhost:3306/your_dbname_here'
user = 'db_username_here'
pwd = 'db_password_here'
catalog = 'name_here' # set this to None to search across all catalogs
query = "select count(*) from ? where lower(?) like '%searh_string_here%'"
skipSize = 1000

# START of the script
Class.forName( driver)
conn = DriverManager.getConnection( url, user, pwd)
meta = conn.getMetaData()
# fetch and store table names from the catalog
tables = meta.getTables( catalog, None, None, None)
tableNames = []
while tables.next():
    tableNames.append( tables.getString(3))
# in each table
for table in tableNames:
    # check total rows in the table
    count = 0
    stmt = conn.createStatement()
    query = 'select count(*) from ' + table
    rs = stmt.executeQuery( query)
    rs.next()
    count = rs.getInt(1)
    rs.close()
    stmt.close()
    # check count
    if count > 1000:
        print 'SKIPPING', table
    else:
        print 'CHECKING', table
        # fetch all columns
        cols = meta.getColumns( catalog, None, table, None)
        while cols.next():
            # look for a string column
            type = cols.getInt(5)
            if type == Types.CHAR or type == Types.VARCHAR:
                colName = cols.getString(4)
                #ps = conn.prepareStatement( query)
                #ps.setString( 1, table)
                #ps.setString( 2, colName)
                #rs = ps.executeQuery()
                stmt = conn.createStatement()
                query = "select count(*) from " + table + " where lower( " + colName
+ ") like '%search_string_here%'"
                rs = stmt.executeQuery( query)
                if rs.next():
                    count = rs.getInt(1)
                    if count > 0:
                        print table, '->', colName, ':', rs.getInt(1)
                rs.close()
                #ps.close()
                stmt.close()
conn.close()