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, please ensure you test any script taken from my website on a test/development machine, before running on a production server

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