How to: Find the Last Modified Date of The Database

This one will list all online databases on the server and the last modified date

DECLARE @sqlString NVARCHAR(max)
DECLARE @union NVARCHAR(max)
SET @sqlString = ''
SET @union = ''
DECLARE @name nvarchar(50);

DECLARE crs CURSOR FOR 
SELECT Name FROM sys.databases WHERE  state = 0
OPEN crs
FETCH NEXT FROM crs INTO @name

WHILE @@FETCH_STATUS = 0
BEGIN
   SET @sqlString = @sqlString + @union 
   SET @sqlString = @sqlString + '
    SELECT 
    TOP 1
  ''' + @name + ''' as DBName, modify_date
  FROM
   [' + @name + '].sys.tables'

 SET @union = ' UNION '

    FETCH NEXT FROM crs INTO @name
END 

SET @sqlString = @sqlString + ' ORDER BY DBName ASC'

CLOSE crs;
DEALLOCATE crs;

EXEC(@sqlString)

4 Replies to “How to: Find the Last Modified Date of The Database”

    1. Hi Ahmet,

      Thank you for your question. With my knowledge in SQL Server, I, personally, do not think that there is a way to query the last modified date of the row from system database. I would suggest you to insert a time-stamp instead, if you are developer. For identifying modified columns in the table, you can either enable the audit trail on specific database or set up your own script to monitor the DDL query that execute on the server. However, for last modified date time on the table, you may execute the following script (from Panel Dave) to verify your table. I hope the above methods help.

      SELECT OBJECT_NAME(OBJECT_ID) AS DatabaseName, last_user_update,*
      FROM sys.dm_db_index_usage_stats
      WHERE database_id = DB_ID( 'AdventureWorks')
      AND OBJECT_ID=OBJECT_ID('test');
      
  1. Hi Jimmy
    I used your query on an instance with 2400 databases. The catalog is so large it could not build an execution plan.
    Error: Msg 8623, Level 16, State 1, Line 2
    The query processor ran out of internal resources and could not produce a query plan. This is a rare event and only expected for extremely complex queries or queries that reference a very large number of tables or partitions. Please simplify the query. If you believe you have received this message in error, contact Customer Support Services for more information.

    Doug

    1. Hi Doug,

      The error message “8623” indicated that the query processor ran out of internal resources and could not produce a query plan. Do you mind to confirm whether your server/workstation has sufficient memory?

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.