Proxy > Gmail Facebook Yahoo!

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


Responses

0 Respones to "Return the MAX of all dates within all tables within all databases"


Send mail to your Friends.  

Expert Feed

 
Return to top of page Copyright © 2011 | My Code Logic Designed by Suneel Kumar