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.

Display the month name in SQL Server

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?