Finding columns in a database

If you need to determine which columns in a database contain a certain type of string, the pseudocode is fairly simple: list all tables in a database, list their columns that are text-oriented, filter those that match some given format. For large tables, this script won’t bother looking at too much.

############################################
# Imports
import MySQLdb
import sys
############################################
# Initialize the database connection
database = MySQLdb.connect(
  host = "localhost",
  user = "username",
  passwd = "password",
  db = "database")
cursor = database.cursor();
############################################
# Find all matching columns
cursor.execute("SHOW TABLES")
tables = [table[0] for table in cursor.fetchall()]
for table in tables:
  cursor.execute("DESCRIBE %s" % table)
  columns = [column for column in cursor.fetchall()]
  for column in columns:
    if column[1].count("text") or column[1].count("char"):
      cursor.execute("SELECT `%s` FROM \
        (SELECT `%s` FROM `%s` LIMIT 20000) AS subtable \
        WHERE `%s` LIKE %s LIMIT 1" \
        % (column[0], column[0], table, column[0], '"%foo%"'))
      results = cursor.fetchall()
      if results != ():
        print "%s.%s -- %s" % (table, column[0], results)
############################################
# Close the database connection
database.close()