SQL: Get Last Used (Max) Date from each Date field in a database

The Transact-SQL script below 1 returns the maximum date (i.e. last used) for each table within a database:

 
DECLARE @TableName VARCHAR(255)
DECLARE @ColumnName VARCHAR(255)
DECLARE @SQL NVARCHAR(1000)
DECLARE @SQLParam NVARCHAR(100)
DECLARE @MaxDate DATETIME

CREATE TABLE #LastUsed
( TableName VARCHAR(255),
  ColumnName VARCHAR(255),
  MaxDate DATETIME
)

DECLARE csr CURSOR FAST_FORWARD FOR
	SELECT O.name AS TableName, C.name AS ColumnName
	FROM sysobjects O
	JOIN syscolumns C ON O.ID = C.ID
	JOIN systypes T ON C.xtype = T.xtype
	WHERE T.name like '%date%' AND O.xtype = 'U'
	ORDER BY O.name, C.name

OPEN csr

FETCH NEXT FROM csr INTO @TableName, @ColumnName

WHILE @@FETCH_STATUS = 0
BEGIN
	SET @SQL = 'SELECT @MaxDateOUT = MAX([' + @ColumnName + ']) FROM [' + @TableName + ']'
	SET @SQLParam = '@MaxDateOUT DATETIME OUTPUT'
	EXEC sp_ExecuteSQL @SQL
	                 , @SQLParam
			 , @MaxDateOUT = @MaxDate OUTPUT;
	INSERT INTO #LastUsed (TableName, ColumnName, MaxDate)
	VALUES (@TableName, @ColumnName, @MaxDate)
	FETCH NEXT FROM csr INTO @TableName, @ColumnName
END

CLOSE csr
DEALLOCATE csr

SELECT * FROM #LastUsed ORDER BY MaxDate DESC
DROP TABLE #LastUsed
  1. Please ensure you test any script taken from my website on a test/development machine, before running on a production server.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.