Return Latest Row DateTime For All Tables in a SQL Server Database

I have a database [database1] that has thousands of tables within it. Each Table has a Row Insert Update Date/Time Column [EventDateTime] in them, not necessarily as a primary key.

I was hoping to create a query that creates a temporary table (or a print view) with two columns in it; [TableName] & [LatestEventDateTime].

I am thinking I might be able to use sp_MSforeachtable to retrieve the data, but I am not sure how to call the max [EventDateTime] for each table.

These would be a great validation query to confirm every table is being updated.

Solution

Yes you can do it like that:

USE tempdb

IF OBJECT_ID(N'#temp') IS NOT NULL DROP TABLE #temp

CREATE TABLE #temp (
    TableName nvarchar(max),
    MaxEventDateTime datetime
)

USE YourDatabaseName

INSERT INTO #temp
exec sp_MSforeachtable 'SELECT ''?'', MAX([EventDateTime]) FROM ?'

SELECT *
FROM #temp

Note: sp_MSforeachtable is undocumented stored procedure and I don't recommend to use it on vital production processes.

I suggest you dynamic SQL solution:

USE tempdb

IF OBJECT_ID(N'#temp') IS NOT NULL DROP TABLE #temp

CREATE TABLE #temp (
    TableName nvarchar(max),
    MaxEventDateTime INT
)

USE YourDatabaseName

DECLARE @sql nvarchar(max)

SELECT @sql = (
    SELECT 'INSERT INTO #temp SELECT '''+[name]+''', MAX([EventDateTime]) FROM '+QUOTENAME([name])+';'+CHAR(10)
    FROM sys.tables 
    FOR XML PATH('')
)

--PRINT @sql
EXEC sp_executesql @SQL

PRINT @sql will bring you query like this:

USE [Test] INSERT INTO #temp SELECT 'TABLE_A', COUNT(*) FROM [TABLE_A];
USE [Test] INSERT INTO #temp SELECT 'TABLE_B', COUNT(*) FROM [TABLE_B];
USE [Test] INSERT INTO #temp SELECT 'TestXml', COUNT(*) FROM [TestXml];
USE [Test] INSERT INTO #temp SELECT 'Clients', COUNT(*) FROM [Clients];

I advice you to use dynamic SQL solution.