Most recent datetime column and count for each table

Question

I have a DB that has 1000+ tables. 100 of those tables are prefixed with a three letters (let's say 'ABC') Only half of those prefixed tables have MODIFIEDDATETIME column.

I'm trying to do a simple select query to get all the last updated MODIFIEDDATETIME stamp for each Table that actually has a MODIFIEDDATETIME on that table and also begins with the three letter prefix.

I've tried using this function but it doesn't seem to be getting me there. Thoughts?

sp_msforeachtable '

select ''?'', modifieddatetime, count(*)
from ? 

where ? like ''%ABC%''
group by modifieddatetime
order by modifieddatetime desc
'

To match only a prefix your pattern string should be `ABC%`, i.e. drop the leading percent sign. — HABO, Apr 23 '12 at 14:34

Aaron Bertrand · Answer 1 · 2021-12-28T02:47:32.757

Borrowing from another answer earlier today:

For one, I recommend staying away from undocumented and unsupported procedures like sp_MSForEachTable. They can be changed or even removed from SQL Server at any time, and this specific procedure may have the same symptoms reported by many against sp_MSForEachDb. (See some background here and here.)

...but also see sp_ineachdb.

Here is how I would do it - most importantly, pull the row count from the metadata which - while not 100% accurate to the millisecond is usually close enough - will not bog down your system performing a scan of every single table:

DECLARE @sql NVARCHAR(MAX);
SELECT @sql = N'';

CREATE TABLE #x
(
    [table]    NVARCHAR(255), 
    updated    DATETIME, 
    [rowcount] BIGINT
);

SELECT @sql = @sql + N'INSERT #x SELECT '''
  + QUOTENAME(OBJECT_SCHEMA_NAME([object_id]))
  + '.' + QUOTENAME(OBJECT_NAME([object_id])) + ''', 
  MAX(MODIFIEDDATETIME), (SELECT SUM(rows) FROM sys.partitions
    WHERE [object_id] = ' + CONVERT(VARCHAR(12), [object_id]) 
    + ') FROM ' + QUOTENAME(OBJECT_SCHEMA_NAME([object_id]))
  + '.' + QUOTENAME(OBJECT_NAME([object_id])) + ';'
FROM sys.columns 
  WHERE UPPER(name) = 'MODIFIEDDATETIME'
  AND UPPER(OBJECT_NAME([object_id])) LIKE 'ABC%';

EXEC sp_executesql @sql;

SELECT [table],updated,[rowcount] FROM #x;

DROP TABLE #x;

That said, I don't know if using MAX(MODIFIEDDATETIME) is appropriate for knowing when a table was touched last. What if a transaction failed? What if the last operation was a delete?

GarethD · Accepted Answer · 2012-04-23T15:09:36.303

2

You could do it with dynamic SQL, but this will probably not be very efficient on 1000 tables!

DECLARE @SQL NVARCHAR(MAX) = ''

SELECT  @SQL = @SQL + ' UNION SELECT COUNT(' + QUOTENAME(Column_Name) + ') [Rows], MAX(' + QUOTENAME(Column_Name) + ') [MaxModifiedDate], ''' + QUOTENAME(Table_Schema) + '.' + QUOTENAME(Table_Name) + ''' [TableName] FROM ' + QUOTENAME(Table_Schema) + '.' + QUOTENAME(Table_Name)
FROM    INFORMATION_SCHEMA.COLUMNS
WHERE   Column_Name = 'ModifiedDateTime'
AND     Table_Name LIKE 'ABC%'

SET @SQL = 'SELECT MaxModifiedDate, TableName, Rows FROM (' + STUFF(@SQL, 1, 7, '') + ') t ORDER BY MaxModifiedDate DESC'
print @sql
EXEC SP_EXECUTESQL @SQL

It basically builds a query like

SELECT  MaxModifiedDate, TableName, Rows
FROM    (   SELECT  'Table1' [TableName], MAX(ModifiedDate) [MaxModifedDate], COUNT(ModifiedDate) [Rows]
            FROM    Table1
            UNION
            SELECT  'Table2' [TableName], MAX(ModifiedDate) [MaxModifedDate], COUNT(ModifiedDate) [Rows]
            FROM    Table2
            UNION
            SELECT  'Table3' [TableName], MAX(ModifiedDate) [MaxModifedDate], COUNT(ModifiedDate) [Rows]
            FROM    Table3
            UNION
            ...
        ) c
ORDER BY MaxModifiedDate DESC

edited Apr 23 '12 at 15:09

answered Apr 23 '12 at 14:29

GarethD

68,045
10
83
123

This is the accepted answer? It doesn't get the count (which you asked for as well) and it only retrieves a single table (the one with the last modified date/time value) when you asked for *all* tables. – Aaron Bertrand Apr 23 '12 at 14:57
I have removed the top 1, and added a count of rows with a modified date. I agree with your approach of using the meta data for the row count, however have taken a different approach to give the OP options. – GarethD Apr 23 '12 at 15:11
really i didn't need the count but that was a bonus. GarethD's answered worked perfectly for my needs! thank you! – aherrick Apr 23 '12 at 15:54
Fair enough, if count wasn't important then it shouldn't be part of the question. :-) – Aaron Bertrand Apr 23 '12 at 16:25
As an aside, here is why I prefer sys.columns over INFORMATION_SCHEMA.COLUMNS : https://sqlblog.org/blogs/aaron_bertrand/archive/2011/11/03/the-case-against-information-schema-views.aspx – Aaron Bertrand Apr 24 '12 at 03:09

Most recent datetime column and count for each table

2 Answers2