Select Page
This entry has been published on 2014-09-25 and may be out of date.

Last Updated on 2014-09-25.

Scenario

You have to move a MySQL database table to another database, but you have many other tables, functions, routines, events etc. which access it. Before moving it, it is not enough to find only the tables containing foreign keys, but also find functions etc. which use it.

This PHP script does that for you: mysql_table_references

Usage

Edit the script to enter your DB server login data, then run it in your browser and enter the table name which you want to find references for.

Script code

<!DOCTYPE html>
<html>
<head>
 <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.2.0/css/bootstrap.min.css">

</head>
<body>
<?php
    $searchtable = @$_GET['searchtable'];
?>

<h1>References, Views and Routines regarding Table</h1>

<form action="<?=$_SERVER['PHP_SELF']?>" method="get">
Table name: <input type="text" name="searchtable" value="<?=@$searchtable?>">
<input type="submit" value="Search">
</form>

<?php
    if (!$searchtable)
        exit;

?>

<?php

$dbhost = 'localhost';
$dbname = 'yourdb';
$dbuser = 'root';
$dbpw = 'yourpw';

try
{
    $db = new PDO("mysql:host=$dbhost;", $dbuser, $dbpw);
}
catch(PDOException $ex)
{
    echo "<b>Could not connect to server! Please make sure you have set the correct config values in this file.</b>";
    echo "<br>Details:<br>".$ex->getMessage();
    exit;
}

//Foreign Keys:
$result = $db->query("select
  TABLE_NAME,COLUMN_NAME,CONSTRAINT_NAME, TABLE_SCHEMA, REFERENCED_TABLE_NAME,REFERENCED_COLUMN_NAME
from INFORMATION_SCHEMA.KEY_COLUMN_USAGE
where
  REFERENCED_TABLE_NAME = '$searchtable' or TABLE_NAME = '$searchtable'
  order by TABLE_NAME, REFERENCED_TABLE_NAME;");
//Alternative?
//  show create table $searchtable;
?>

<div>Note: Results may contain unrelated entries if the table name is not unique, e.g. if it is also used in column names etc.</div>

<hr>
<h2>Results for <b><?=$searchtable?></b></h2>

<h3>Foreign Keys</h3>
<table border="1">
<tr>
    <th>DB</th>
    <th>Table</th>
    <th>Column</th>
    <th>Referenced Table</th>
    <th>Referenced Column</th>
</tr>

<?php
while($row = $result->fetch())
{
     ?>
     <tr>
        <td><?=$row['TABLE_SCHEMA']?></td>
        <td><?=$row['TABLE_NAME']?></td>
        <td><?=$row['COLUMN_NAME']?></td>
        <td><?=$row['REFERENCED_TABLE_NAME']?></td>
        <td><?=$row['REFERENCED_COLUMN_NAME']?></td>
     </tr>
     <?php
}
?>
</table>
<?php

//Usage in Views:
$result = $db -> query("select
    TABLE_SCHEMA, TABLE_NAME, VIEW_DEFINITION
    from INFORMATION_SCHEMA.views
    where view_definition like '%from%$searchtable%'
");

?>
<h3>Views</h3>
<table border="1">
<tr>
    <th>DB</th>
    <th>Table</th>
    <th>View Definition</th>
</tr>

<?php

while($row = $result->fetch())
{
     ?>
     <tr>
        <td><?=$row['TABLE_SCHEMA']?></td>
        <td><b><?=$row['TABLE_NAME']?></b></td>
        <td><small><?=nl2br(str_ireplace($searchtable, "<b>$searchtable</b>", $row['VIEW_DEFINITION']))?></small></td>
     </tr>
     <?php
}

?>
</table>
<?php

//Usage in Routines:
$result = $db -> query("select
    ROUTINE_SCHEMA, ROUTINE_NAME, ROUTINE_DEFINITION
    from INFORMATION_SCHEMA.ROUTINES
    where routine_definition like '%from%$searchtable%'
");
?>
<h3>Routines</h3>
<table border="1">
<tr>
    <th>DB</th>
    <th>Table</th>
    <th>Routine Definition</th>
</tr>

<?php

while($row = $result->fetch())
{
     ?>
     <tr>
        <td><?=$row['ROUTINE_SCHEMA']?></td>
        <td><b><?=$row['ROUTINE_NAME']?></b></td>
        <td><small><?=nl2br(str_ireplace($searchtable, "<b>$searchtable</b>", $row['ROUTINE_DEFINITION']))?></small></td>

     </tr>
     <?php
}

?>
</table>
<?php

//Usage in Events:
$result = $db -> query("select
    EVENT_SCHEMA, EVENT_NAME, EVENT_DEFINITION
    from INFORMATION_SCHEMA.events
    where event_definition like '%from%$searchtable%'
");

?>
<h3>Events</h3>
<table border="1">
<tr>
    <th>DB</th>
    <th>Table</th>
    <th>Event Definition</th>
</tr>

<?php

while($row = $result->fetch())
{
     ?>
     <tr>
        <td><?=$row['EVENT_SCHEMA']?></td>
        <td><b><?=$row['EVENT_NAME']?></b></td>
        <td><small><?=nl2br(str_ireplace($searchtable, "<b>$searchtable</b>", $row['EVENT_DEFINITION']))?></small></td>

     </tr>
     <?php
}

?>
</table>

<script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.2.0/js/bootstrap.min.js"></script>
</body>
</html>

It might also be useful to get all DB users which have access to the table(s). Use phpMyAdmin for this (Activate table, then click “Permissions”).

Moving tables to another database might have effect to a lot of software accessing the data, and it could take a long time to get the code updated. During this process, use a workaround like table views. You can reference the new table with the view, so it is accessible via both databases. Using MySQL, you can e.g. use “update” or “insert” commands directly on the view in many cases, e.g. if there is exactly 1 table in the view query.

Example: create view address as select * from newdb.address