I want to find the most recent date in a database.

I could use sys.dm_db_index_usage_stats to find when it was last updated, but there is no value here if it has not been updated since the last server restart

I could use MAX()

use adventureworks
select Max(ModifiedDate)
from HumanResources.Department

But I would need to look at every table in the database, find each datetime (or date, smalldatetime, datetime2) field and run the query multiple times, then decide which was most recent.

I want something like

use <some_db>
select MAX(<FieldThatIsADate>)
from <anytable_in_db>

I have Googled around and am not finding a way to use the datatype in place of a field name. I have not attempted to find a way around naming the specific table.

How can I make this query as portable as realistic?

If I have to build and run a unique query for every table in each database, well that is what I have to do.

  • 2
    Can you define "portable"? Also, how do you expect SQL Server to know which datetime columns you use for "modified" and which are never updated? What if there is a column that holds the future date of some event? What if there is a column that at one point held the future date of some event, but that event passed 5 minutes ago? Feb 23, 2016 at 13:48
  • Portable = As database agnostic as possible. If all the dates are some length of time in the past, I can make arguments that further research is without merit. If the future event was 5 minutes ago, more research is needed. I was hoping to somehow scan through all the datetime columns in the table and only select the MAX date. I am not overly concerned with performance impact as I will have already identified the db as not currently active. Feb 23, 2016 at 14:07
  • If it is not possible to use the datatype in the select MAX(), than the I don't see any option other than unique queries. Imagine you all the sample databases Microsoft has created, spread out on multiple servers, some are duplicated, some are not. If you can find the max date in each that will give you some guide as to when a particular database was used. Feb 23, 2016 at 14:20
  • 3
    You could get there with dynamic SQL traversing sys.tables, sys.objects and sys.columns and build your own queries using the information available. See this for inspiration. Then again I'm not going to write that :)
    – Tom V
    Feb 23, 2016 at 14:26
  • Cursor will do,building tables which has datetime columns and selecting only max datetime column from each table into a temp table and finally max(datetime) in temp table will work.Further if you have frequent inserts max value will not be max anymore and may change before the query completes Feb 23, 2016 at 14:40

3 Answers 3


There is no way to query on a datatype much less across various tables like that, it would imply that the entire database is kept a second time in a set of tables that resemble the system tables. However, dynamic SQL using the INFORMATION_SCHEMA can be of assistance in your search, but it will not be portable to other types of SQL Servers. Most servers should have something similar available but the syntax will be radically different for each one.

You can do the following to find what you're looking for:

SELECT 'SELECT max(modifiedData) FROM ('+
        (SELECT 'SELECT MAX(['+C.COLUMN_NAME+']) as modifiedData FROM ['+C.TABLE_SCHEMA+'].['+C.TABLE_NAME+'] UNION ' AS [text()]
        WHERE C.DATA_TYPE IN ('date','datetime','smalldatetime','datetime2')
        FOR XML PATH('')) + ') AS AllDateTimes'

That dynamic SQL will output a query you can run to find your MAX date. Please note that you will need to trim a trailing UNION from the inner SELECT after you've copied the output to a query window. You can create the same query with the sys tables but you will have to join a few of them together and the query will be a bit more complex if you aren't familiar with how the sys tables are setup.

That all said, this will return the maximum date stored in your database regardless of context. Depending on how it was used there is no guarantee some value could not have been set in the 'future' for any number of reasons.

  • Thank you this is working great on small databases, I am playing with it, I have found that if you run the output query against a large database with many date time fields (like adventureworks) it has issues. I am playing around with how to best idenityf how where to break the query. Also you did not include the datatype 'date' in line 4 of your master query, is there a reason? I put it in for early testing and it seemed to work. Feb 23, 2016 at 15:44
  • In theory there is not limit to unions, stackoverflow.com/questions/6676970/… but in practice here it seems to be a problem Feb 23, 2016 at 15:45
  • Oversight on the date thing, just missed it. I'll update it for anyone's future reference. Are you getting an error or is it just slow? It takes several minutes to run against one of our databases and outputs a query that is about 31,514 characters long.
    – Duffy
    Feb 23, 2016 at 15:45
  • I just practicing against adventure works, returning the output query quickly and runs or fails quickly I get messages like Msg 4104, Level 16, State 1, Line 27 The multi-part identifier "Emp.StartDate" could not be bound. I am putting returns prior to each UNION SELECT and narrowing down where the issue occurs. Feb 23, 2016 at 15:52
  • Loaded up AdventureWorks and I figured it out, looks like some of their column names have dot notation in them which is getting interpreted as an alias. I updated my answer with brackets to force the literal interpretation of the names for columns and tables. Should work now.
    – Duffy
    Feb 23, 2016 at 16:09

This will prefer to use the last_user_update value in sys.dm_db_index_usage_stats, otherwise fetch the combined maximum value of any present date fields, which should reduce run-time.

SET @sql = ''

;with dmv AS (
    SELECT  [object_id], MAX(last_user_update) [max_date], CAST('index_usage_stats' AS VARCHAR(200)) [date_source]
    FROM    sys.dm_db_index_usage_stats s
    WHERE   s.database_id = DB_ID()
    GROUP BY [object_id]
SELECT  s.name [table_schema], t.name [table_name], [max_date], [date_source]
INTO    #results
FROM    sys.tables t
JOIN    sys.schemas s ON s.schema_id = t.schema_id
LEFT JOIN dmv d ON d.object_id = t.object_id AND d.max_date IS NOT NULL

SELECT  @sql = @sql + ';with dates AS (
    SELECT  [max_date], [date_source]
    FROM    #results
    WHERE   table_schema = ''' + r.table_schema + ''' AND table_name = ''' + r.table_name + '''
    SELECT  MAX([' + COLUMN_NAME + ']) [max_date], ''' + COLUMN_NAME + ''' [date_source] FROM [' + r.table_schema + '].[' + r.table_name + ']
), maxDate AS (
    SELECT  [max_date], [date_source], ROW_NUMBER () OVER (ORDER BY [max_date] DESC) [rn]
    FROM    dates
UPDATE  #results
SET     max_date = m.[max_date], date_source = m.date_source
FROM    maxDate m
WHERE   table_schema = ''' + r.table_schema + ''' AND table_name = ''' + r.table_name + ''' AND 
        rn = 1
FROM    #results r
JOIN    INFORMATION_SCHEMA.COLUMNS c ON c.table_schema = r.table_schema AND c.table_name = r.table_name AND c.data_type IN ('date','datetime','smalldatetime','datetime2')
WHERE   r.[max_date] IS NULL

exec sp_executesql @sql

FROM #results
ORDER BY max_date DESC

DROP TABLE #results

this query can give all datetime columns with their max value in a table on sql server. latest date values are on the top.


INTO #datetimeColumns
WHERE DATA_TYPE  IN ('datetime', 'smalldatetime', 'datetime2');


DECLARE cursor_datetimeColumns CURSOR FOR
SELECT ID, Script FROM #datetimeColumns;

OPEN cursor_datetimeColumns;
FETCH NEXT FROM cursor_datetimeColumns INTO @ID, @Script;

        PRINT 'No datetime column in this database'     


            EXEC sys.sp_executesql @script, N'@Result varchar(50) output', @Result OUTPUT;

            UPDATE #datetimeColumns
            SET Value = @Result
            WHERE ID = @ID;

            FETCH NEXT FROM cursor_datetimeColumns INTO @ID, @Script;
CLOSE cursor_datetimeColumns;
DEALLOCATE cursor_datetimeColumns;

FROM #datetimeColumns

DROP TABLE #datetimeColumns;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.