3

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? – Aaron Bertrand Feb 23 '16 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. – James Jenkins Feb 23 '16 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. – James Jenkins Feb 23 '16 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 - try topanswers.xyz Feb 23 '16 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 – TheGameiswar Feb 23 '16 at 14:40
8
+50

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.

  • 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. – James Jenkins Feb 23 '16 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 – James Jenkins Feb 23 '16 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 '16 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. – James Jenkins Feb 23 '16 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 '16 at 16:09
0

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.

DECLARE @sql NVARCHAR(MAX)
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 + '''
    UNION
    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

SELECT * 
FROM #results
ORDER BY max_date DESC

DROP TABLE #results
-1

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

SET NOCOUNT ON;

SELECT ROW_NUMBER() OVER(ORDER BY TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME) AS ID, TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, COLUMN_DEFAULT, IS_NULLABLE, DATA_TYPE, 'SELECT @Result=CAST(MAX(['+COLUMN_NAME +']) AS DATETIME2)  FROM [' + TABLE_SCHEMA + '].['+ TABLE_NAME +']'  AS Script, CAST(NULL AS VARCHAR(MAX)) AS Value
INTO #datetimeColumns
FROM INFORMATION_SCHEMA.COLUMNS
WHERE DATA_TYPE  IN ('datetime', 'smalldatetime', 'datetime2');


DECLARE @ID INT, @script NVARCHAR(MAX), @Result VARCHAR(50);

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

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

IF @@FETCH_STATUS <> 0 
        PRINT 'No datetime column in this database'     

    WHILE @@FETCH_STATUS = 0
    BEGIN

            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;
    END 
CLOSE cursor_datetimeColumns;
DEALLOCATE cursor_datetimeColumns;


SELECT *
FROM #datetimeColumns
ORDER BY CAST(Value AS DATETIME2) DESC;


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.