List child tables

from the Artful MySQL Tips List


A reason for the existence of the set of Views known as information_schema is the need for standardised database metadata queries. Unfortunately, queries against the MySQL information_schema can be painfully slow, and MySQL AB spokespersons have told us not to expect a fix any time soon. We solved the problem in theUsual by writing a function that can either query information_schema or parse the results of iterative SHOW TABLES commands. The SHOW TABLES method is 10-50 times faster depending on the environment. This version looks for child tables in one database; it is a bit slower when modified to search all server DBs, but even then it is much faster than an information_schema query. It assumes an available connection object $conn:

<?php
function childtables$db$table$via_infoschema=FALSE ) {
  GLOBAL 
$conn;
  
$ret = array();
  if( 
$via_infoschema ) {
    
$res mysqli_querychildtablesqry$conn$db$table )) || die( mysqli_error($conn) );
    if( !
is_bool$res )) 
      while( 
$row mysqli_fetch_row$res )) 
        
$ret[] = $row;
  }
  else {
    
$tables = array();
    
$res mysqli_query$conn"SHOW TABLES" );
    while( 
$row mysqli_fetch_row$res )) $tables[] = $row[0];
    foreach( 
$tables as $referringtbl ) {
      
$res mysqli_query"SHOW CREATE TABLE $referringtbl);
      
// SHOW CREATE TABLE returns 2 cols: tblname, create stmt
      
$row mysqli_fetch_row$res );
      
$s " REFERENCES "$table" ("";
      if(( 
$start = stripos( $row[1]$s )) > 0 ) {
        
$end = strpos( $row[1], '"', $start + strlen( $s ));
        $referencedcol = substr( $row[1], $start+strlen($s), $end-$start-strlen($s) ); 
        $s = "FOREIGN KEY ("";
        if(( $start = stripos( $row[1], $s )) > 0 ) {    
          $end = strpos( $row[1], '"', 
$start + strlen( $s )); 
          
$referencingcol = substr( $row[1]$start+strlen($s), $end-$start-strlen($s) );  
        } 
        // array cols: dbname, table_name, 
        // referencing_column_name, referenced_column_name        
        
$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 " .
         "
INNER JOIN information_schema.key_column_usage AS " .
         "
USINGconstraint_schemaconstraint_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";
}
?>

Return to the Artful MySQL Tips page