Fetch the modified date of table in SAP HANA

In SAP HANA database, you can fetch the modified date of a table using the system view SYS.M_TABLE_STATISTICS. This view provides statistical information about tables, including their last modification timestamps.

Using M_TABLE_STATISTICS System View

The M_TABLE_STATISTICS system view contains metadata about tables in your SAP HANA database. You can query this table to find information about when a specific table was last modified by ordering the results based on the last modified date.

Example

Here's how to query the table statistics to get the modified date ?

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

You can also query for all tables in a specific schema and order them by modification date ?

SELECT 
    TABLE_NAME,
    LAST_MODIFY_TIME
FROM SYS.M_TABLE_STATISTICS
WHERE SCHEMA_NAME = 'YOUR_SCHEMA_NAME'
ORDER BY LAST_MODIFY_TIME DESC;

Important Note

The M_TABLE_STATISTICS system view is not transactional. This means that when a DML statement is rolled back, the count values and timestamps in this view remain unchanged. The statistics are updated asynchronously and may not reflect the exact real-time state of your tables.

Conclusion

Using the SYS.M_TABLE_STATISTICS system view is the standard approach to fetch table modification dates in SAP HANA, providing essential metadata for database monitoring and maintenance tasks.