1

I need to find out the last update time (or last changed time) of a table in the database (i.e. the last time when data in a specific table is updated, deleted or inserted) for deciding whether a backup is needed or not. As per the MySQL/MariaDB manual, I have used the following SQL statement to get the last modified time of a table in the database:

SELECT update_time FROM information_schema.tables WHERE table_schema='my_db' and table_name='my_table'; -- For table named 'my_table' in database 'my_db'

Here's the problem: The value of the resultant update_time field is always returned NULL even though changes are made in the specified table. What's more unanticipated is that when I manually browse the tables table in the information_schema database, there are no entry of my_db and my_table in table_schema and table_name fields respectively, meaning that the activities are not logged. Can someone please help me solve this problem and explain me the reason behind it?

EDIT: This question is not a duplicate as the question also asks about the fields of information_schema.tables being NULL rather than just asking how to get last update time.

hecate
  • 620
  • 1
  • 8
  • 33
  • I'm pretty sure these values are reset when the server starts. I would recommend using a column in the table itself that contains a timestamp which is updated when the record is updated. – Jacob H May 24 '17 at 13:13

2 Answers2

1

If a table contains UpdatedDate or ModifiedDate date column with default together with value GETDATE (). He should make use of it. On close observation the table is not required to keep history when any row is inserted. However, the sole prerequisite is to be aware of when any table has been updated. That’s it! If a user wants to finds out when was the last table updated he can query dynamic management view (dmv) – sys.dm_db_index_usage_stats and easily figure out when was the table updated last. Let us comprehend this example by creating a table and updating it. We can use dmv to determine when it was updated last.

SELECT OBJECT_NAME(OBJECT_ID) AS DatabaseName, last_user_update,*
FROM sys.dm_db_index_usage_stats
WHERE database_id = DB_ID( 'AdventureWorks')
AND OBJECT_ID=OBJECT_ID('test')
Ahmad Ansari
  • 11
  • 1
0

To get the current database size just by querying into your query browser or CLI from the INFORMATION_SCHEMA database in table TABLES.

SELECT table_schema "Data Base Name", 
    sum( data_length + index_length ) / 1024 / 1024 "Data Base Size in MB" 
    FROM information_schema.TABLES 
    GROUP BY table_schema ;

Get the database last update ordered by update time than by creating time.

SELECT MAX(UPDATE_TIME), MAX(CREATE_TIME), TABLE_SCHEMA 
FROM `TABLES` 
GROUP BY TABLE_SCHEMA 
ORDER BY 1, 2;
Bhupesh
  • 883
  • 7
  • 16