A common DBA job is to check for child tables before carrying out operations on a table that might be a parent. For example, you should not swap-out a table before checking whether that table had dependent children. You may have nice tools for listing dependent tables, but the cheap and reliable way is to search a (fresh) schema dump. It’s trivial to grep for a table name, but if that throws up foreign key constraints, you want to know the table names that contain them.

Unfortunately grep doesn’t have the nice show-function-line parameter that diff has, but you can get round that by pulling out the table name for every table at the same time as looking for matches. That way a match will have the corresponding table name above it.

For example, you want to search for any children of table tCandidate in a MySQL v4.0 database.

for d in $(mysql -NBe"show databases"); do
  echo $d;
  mysqldump --no-data --skip-comments --skip-quote-names $d >schema__${d}.sql;
done
grep -lF tCandidate schema__*.sql
grep '\(^CREATE\|tCandidate\)' schema__Products.sql | less

That shows you which schemata have potential matches, then for each of those schemata you any matches will be preceded by the 'CREATE' line showing the table name.

Advertisements