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