Sql-server – How to find the max date stamp in a database

datetimesql server

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.

Best Answer

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()]
        FROM INFORMATION_SCHEMA.COLUMNS C
        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.

Related Question