find out the last update date of MySQL table

SELECT UPDATE_TIME FROM information_schema.tables WHERE  TABLE_SCHEMA = 'dbName' AND TABLE_NAME = 'tableName'

<link to competing site deleted by COBOLdinosaur, Topic Advisor>

but this one:

SELECT UPDATE_TIME FROM information_schema.tables WHERE  TABLE_SCHEMA   = 'dbName' AND TABLE_NAME = 'tableName'

means I have to plug-in the dbname and tableName valarble ?

and this statement is not going to do for all tables, right?

I might need to do this:

DECLARE cursor-name CURSOR FOR
SELECT DISTINCT TABLE_NAME
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_SCHEMA='<DB name>';  
?

how to construct this kind of CURSOR ?

e.g.:
 OPEN cursor-name;                                
FETCH cursor-name INTO variable [, variable];    
CLOSE cursor-name;

how to make use of this:

1.DELIMITER //  
2.  
3.CREATE PROCEDURE `proc_CURSOR` (OUT param1 INT)  
4.BEGIN  
5.    DECLARE a, b, c INT;  
6.    DECLARE cur1 CURSOR FOR SELECT col1 FROM table1;  
7.    DECLARE CONTINUE HANDLER FOR NOT FOUND SET b = 1;  
8.    OPEN cur1;  
9.  
10.    SET b = 0;  
11.    SET c = 0;  
12.     
13.    WHILE b = 0 DO  
14.        FETCH cur1 INTO a;  
15.        IF b = 0 THEN  
16.            SET c = c + a;  
17.    END IF;    
18.    END WHILE;  
19.  
20.    CLOSE cur1;  
21.    SET param1 = c;  
22.  
23.END //  

Select allOpen in new window

why all update time return NULL ?

it seems that the query is not that one, right?

Add your own credentials at line 10 and run this to see the moving parts.  HTH, ~Ray

<?php // RAY_temp_marrowyung.php
ini_set('display_errors', TRUE);
error_reporting(E_ALL);
echo '<pre>';

// THIS SCRIPT DEMONSTRATES HOW TO FIND THE LAST UPDATE FOR TABLES
// http://www.experts-exchange.com/Database/MySQL/Q_28269817.html

// DATABASE CONNECTION AND SELECTION VARIABLES - GET THESE FROM YOUR HOSTING COMPANY
$db_host = "localhost"; // PROBABLY THIS IS OK
$db_name = "??";
$db_user = "??";
$db_word = "??";

// OPEN A CONNECTION TO THE DATA BASE SERVER AND SELECT THE DB
$mysqli = new mysqli($db_host, $db_user, $db_word, $db_name);

// DID THE CONNECT/SELECT WORK OR FAIL?
if ($mysqli->connect_errno)
{
    $err
    = "CONNECT FAIL: "
    . $mysqli->connect_errno
    . ' '
    . $mysqli->connect_error
    ;
    trigger_error($err, E_USER_ERROR);
}

// FIND THE TABLES IN THE DATA BASE
$sql = "SHOW TABLES FROM $db_name";
if (!$res = $mysqli->query($sql))
{
    $err
    = 'QUERY FAILURE:'
    . ' ERRNO: '
    . $mysqli->errno
    . ' ERROR: '
    . $mysqli->error
    . ' QUERY: '
    . $sql
    ;
    trigger_error($err, E_USER_ERROR);
}
// ACCUMULATE THE TABLE NAMES IN AN ARRAY
while ($row = $res->fetch_object())
{
    $key = 'Tables_in_' . $db_name;
    $arr[] = $row->$key;
}

// CHECK THE INFORMATION SCHEMA FOR EACH TABLE NAME
foreach ($arr as $dbt)
{
    $dbt = $mysqli->real_escape_string($dbt);
    $sql = "SELECT UPDATE_TIME FROM information_schema.tables WHERE TABLE_SCHEMA = '$db_name' AND TABLE_NAME = '$dbt' LIMIT 1";
    $res = $mysqli->query($sql);
    if (!$res)
	{
	    $err
	    = 'QUERY FAILURE:'
	    . ' ERRNO: '
	    . $mysqli->errno
	    . ' ERROR: '
	    . $mysqli->error
	    . ' QUERY: '
	    . $sql
	    ;
	    trigger_error($err, E_USER_ERROR);
	}

    // SHOW THE RESULTS
    $row = $res->fetch_object();
    $tme = $row->UPDATE_TIME;
    if (empty($tme)) $tme = 'NOT UPDATED';
    echo PHP_EOL . "$tme $dbt";
}

Select allOpen in new window

ok, this is PHP script ? someone surprising to grep me the update file date of the MySQL data file from linux much reliable, surprise !

you are right that this one works, not from MyPHPadmin but Toad or any UI.

SELECT TABLE_NAME,UPDATE_TIME FROM information_schema.tables
WHERE  TABLE_SCHEMA   = 'dbName'
ORDER BY UPDATE_TIME DESC