Most recent datetime column and count for each table

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
'

Answer

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