Find child tables
Starting with MySQL 5, you can find all tables which have foreign key references to a given table with an information_schema query, here encapsulated in a stored procedure which takes two arguments, a database name and a table name. When the table argument is blank or NULL, the procedure returns all parent-child links where the parent table is in the specified database; otherwise it returns parent-child links for the specified parent table:
DROP PROCEDURE IF EXISTS ListChildren;
DELIMITER |
CREATE PROCEDURE ListChildren( pdb CHAR(64), ptbl CHAR(64) )
BEGIN
IF ptbl = '' OR ptbl IS NULL THEN
SELECT
c.table_schema as 'Parent Schema',
u.referenced_table_name as 'Parent Table',
u.referenced_column_name as 'Parent Column',
u.table_schema as 'Child Schema',
u.table_name as 'Child Table',
u.column_name as 'Child Column'
FROM information_schema.table_constraints AS c
JOIN information_schema.key_column_usage AS u USING(constraint_schema,constraint_name)
WHERE c.constraint_type = 'FOREIGN KEY'
AND u.referenced_table_schema = pdb
ORDER BY
c.table_schema, u.referenced_table_name, u.referenced_column_name,
u.table_schema, u.table_name, u.column_name;
ELSE
SELECT
c.table_schema as 'Parent Schema',
u.referenced_table_name as 'Parent Table',
u.referenced_column_name as 'Parent Column',
u.table_schema as 'Child Schema',
u.table_name as 'Child Table',
u.column_name as 'Child Column'
FROM information_schema.table_constraints AS c
JOIN information_schema.key_column_usage AS u USING(constraint_schema,constraint_name)
WHERE c.constraint_type = 'FOREIGN KEY'
AND u.referenced_table_schema = pdb
AND u.referenced_table_name = ptbl
ORDER BY
c.table_schema, u.referenced_table_name, u.referenced_column_name,
u.table_schema, u.table_name, u.column_name;
END IF;
END |
DELIMITER ;
Unfortunately it performs slowly, not because of how it is written, but because of how MySQL has implemented information_schema. Indeed a bug report page is devoted to the issue. It says MySQL AB will not be speeding up information_schema query performance any time soon.
In theUsual we worked around this problem with a PHP function that queries information_schema if that is required, but by default parses the results of iterative SHOW TABLES commands. The SHOW TABLES method logic is simple, and will port readily to another application language. It runs 10-50 times faster than the equivalent information_schema query.
The following version looks for child tables in one database; it slows down a bit when modified to search all server DBs, but even then it is much faster than its information_schema equivalent. It assumes an available connection object $conn:
function childtables( $db, $table, $via_infoschema=FALSE ) {
GLOBAL $conn;
$ret = array();
if( $via_infoschema ) {
$res = mysql_query( childtablesqry( $db, $table )) || die( mysql_error() );
if( !is_bool( $res ))
while( $row = mysql_fetch_row( $res ))
$ret[] = $row;
}
else {
$tables = array();
$res = mysql_query( "SHOW TABLES" );
while( $row = mysql_fetch_row( $res )) $tables[] = $row[0];
$res = mysql_query( "SELECT LOCATE('ANSI_QUOTES', @@sql_mode)" );
$ansi_quotes = $res ? mysql_result( $res, 0 ) : 0;
$q = $ansi_quotes ? '"' : "`";
$sref = ' REFERENCES ' . $q . $table . $q . ' (' . $q;
foreach( $tables as $referringtbl ) {
$res = mysql_query( "SHOW CREATE TABLE $referringtbl" );
$row = mysql_fetch_row( $res );
if(( $startref = stripos( $row[1], $sref )) > 0 ) {
$endref = strpos( $row[1], $q, $startref + strlen( $sref ));
$referencedcol = substr( $row[1], $startref+strlen($sref),
$endref-$startref-strlen($sref) );
$endkey = $startref;
while( substr( $row[1], $endkey, 1 ) <> $q ) $endkey--;
$startkey = --$endkey;
while( substr( $row[1], $startkey, 1 ) <> $q ) $startkey--;
$referencingcol = substr( $row[1], $startkey+1, $endkey - $startkey );
$ret[] = array( $db, $referringtbl, $referencingcol, $referencedcol );
}
}
}
return $ret;
}
function childtablesqry( $db, $table ) {
return "SELECT c.table_schema,u.table_name,u.column_name,u.referenced_column_name " .
"FROM information_schema.table_constraints AS c " .
"INNER JOIN information_schema.key_column_usage AS u " .
"USING( constraint_schema, constraint_name ) " .
"WHERE c.constraint_type = 'FOREIGN KEY' " .
"AND u.referenced_table_schema='$db' " .
"AND u.referenced_table_name = '$table' " .
"ORDER BY c.table_schema,u.table_name";
}