Modification date of SQL Server tables and views with a T-SQL query

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.

What are SQL Server system tables?

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.

Be the first to comment

Leave a Reply

Your email address will not be published.


*