SQL: Return the MAX of all dates within all tables within all databases

I wanted to produce a list of the MAX of date fields (DateTime, SmallDateTime, TimeStamp) for all tables within all databases held in an instance of SQL Server.

The script below was written for SQL Server 2000, but should be easily customisable for SQL Server 2005

As always 1, please ensure you test any script taken from my website on a test/development machine, before running on a production server.

USE master
GO

DECLARE @DatabaseID INT
DECLARE @DatabaseName VARCHAR(500)
DECLARE @SQL NVARCHAR(1000)
DECLARE @TableName VARCHAR(100)
DECLARE @FieldName VARCHAR(100)
DECLARE @MaxDate DATETIME

CREATE TABLE #MaxDates
(	DatabaseID		INT,
	DatabaseName	VARCHAR(100),
	TableName		VARCHAR(100),
	FieldName		VARCHAR(100),
	MaxDate			DATETIME
)
 
DECLARE DatabaseCursor CURSOR FAST_FORWARD 
FOR
      SELECT dbid, name
      FROM Master.dbo.sysdatabases
      WHERE name NOT IN ('master', 'msdb', 'tempdb', 'model')
      AND status & 512 = 0
      ORDER BY name

OPEN DatabaseCursor

FETCH NEXT FROM DatabaseCursor
	INTO @DatabaseID, @DatabaseName

WHILE @@FETCH_STATUS = 0
BEGIN
	DECLARE @Tables CURSOR
	SET @SQL = N'SET @Tables = CURSOR STATIC FOR
		SELECT Name FROM [' + @DatabaseName + '].dbo.sysobjects 
		WHERE type = ''U'' 
		ORDER BY Name;
		OPEN @Tables'
	EXEC sp_executesql
		@SQL, N'@Tables cursor OUTPUT', @Tables OUTPUT

	SET NOCOUNT ON

	FETCH NEXT FROM @Tables 
		INTO @TableName

	WHILE @@FETCH_STATUS = 0
	BEGIN
		DECLARE @Fields CURSOR
		-- Select fields that are SMALLDATETIME, DATETIME or TIMESTAMP
		SET @SQL = N'SET @Fields = CURSOR STATIC FOR
			    SELECT C.name FROM [' + @DatabaseName + '].dbo.syscolumns C
				INNER JOIN [' + @DatabaseName + '].dbo.sysobjects T
					ON C.id = T.id
				WHERE T.name = ' + CHAR(39) + @TableName + CHAR(39) + ' 
					AND C.xusertype IN (58, 61, 189)
				ORDER BY C.colid;
			OPEN @Fields'
		EXEC sp_executesql
			@SQL, N'@Fields cursor OUTPUT', @Fields OUTPUT

		FETCH NEXT FROM @Fields 
			INTO @FieldName
		
		WHILE @@FETCH_STATUS = 0
		BEGIN
			SET @SQL = 'INSERT INTO #MaxDates (DatabaseID, DatabaseName, TableName, FieldName, MaxDate) '
			SET @SQL = @SQL + 'SELECT ' + LTRIM(RTRIM(STR(@DatabaseID))) + ' AS dbID, '
			SET @SQL = @SQL + CHAR(39) + @DatabaseName + CHAR(39) + ' AS db, '
			SET @SQL = @SQL + CHAR(39) + @TableName + CHAR(39) + ' AS tbl, '
			SET @SQL = @SQL + CHAR(39) + @FieldName + CHAR(39) + ' AS field, '
			SET @SQL = @SQL + 'MAX([' + @FieldName + ']) AS MaxDate '
			SET @SQL = @SQL + 'FROM [' + @DatabaseName + '].dbo.[' + @TableName + ']'
			EXEC (@SQL)
			FETCH NEXT FROM @Fields 
				INTO @FieldName
		END
		CLOSE @Fields
		DEALLOCATE @Fields
		FETCH NEXT FROM @Tables 
			INTO @TableName
	END
	CLOSE @Tables
	DEALLOCATE @Tables
    FETCH NEXT FROM DatabaseCursor
		INTO @DatabaseID, @DatabaseName
END
CLOSE DatabaseCursor
DEALLOCATE DatabaseCursor

SELECT * FROM #MaxDates
DROP TABLE #MaxDates
  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 e-mail address will not be published. Required fields are marked *

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