Getting details when a table is modified in SAP HANA DB

You can query SYS.M_TABLE_STATISTICS to get modification details by providing the table name and checking the LAST_MODIFY_TIME column. This system view contains statistical information about tables including when they were last modified.

Query Syntax

Here is the sample SQL query to retrieve table modification details ?

SELECT "TABLE_NAME", "LAST_MODIFY_TIME" 
FROM SYS.M_TABLE_STATISTICS
WHERE "TABLE_NAME" = 'YOUR_TABLE_NAME'
ORDER BY "LAST_MODIFY_TIME" DESC;

In the above command, you need to replace YOUR_TABLE_NAME with your actual table name.

Example

To get modification details for a table named EMPLOYEES ?

SELECT "TABLE_NAME", "LAST_MODIFY_TIME" 
FROM SYS.M_TABLE_STATISTICS
WHERE "TABLE_NAME" = 'EMPLOYEES'
ORDER BY "LAST_MODIFY_TIME" DESC;

The output will show something like ?

TABLE_NAME    LAST_MODIFY_TIME
EMPLOYEES     2024-01-15 14:30:25.123000000

Getting Multiple Tables

You can also retrieve modification times for all tables by removing the WHERE clause ?

SELECT "TABLE_NAME", "LAST_MODIFY_TIME" 
FROM SYS.M_TABLE_STATISTICS
ORDER BY "LAST_MODIFY_TIME" DESC;

This will return all tables ordered by their last modification time, with the most recently modified tables appearing first.

Conclusion

The SYS.M_TABLE_STATISTICS view provides an efficient way to track table modifications in SAP HANA DB by querying the LAST_MODIFY_TIME column along with the table name.