How to display the modification date of SQL Server tables?
This SQL Server query example uses the system tables to detect and display the latest modification date of SQL Server tables, or views. It displays the creation date and the last update date. Moreover, this query only takes into account the tables because it reads the data from the system table sys.tables.
How to display the modification date of a SQL Server table?
Indeed, this SQL script displays the list of tables created in the database. I.e., the “non-system” tables, as well as the creation date and the last update date. In addition, the query selects other data from the MS RDBMS system table, like the type and the type description.
Audit the dates of changes in the structure of tables and views
Indeed, the date displayed is about the structural change of the table, not its content. This query is useful in SQL audits, to analyse the latest changes in creation and modification of objects within a database.
SELECT [name], [type], [type_desc], [create_date], [modify_date] FROM sys.tables WHERE name like '%' ORDER BY modify_date DESC, create_date DESC;
Finally, to display the same information for a view, use the same code and replace sys.tables with sys.views. System tables also allow you to display the full month name with SQL Server with a T-SQL query.
MS SQL useful scripts
More T-SQL tutorials and scripts to better manage objects and data.
- How to manage SQL Server views with T-SQL code?
- Store a SQL column in a variable with delimiters
- Modify a SQL Server column with a script
- How to calculate the difference between two dates in SQL Server?
SQL Server system tables are tables used by the database management system and the SQL engine. In effect, these tables store the list of SQL Server objects and allow the database to function. It is strongly recommended that you do not edit these tables directly.