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
Labels:
SQL
Responses
0 Respones to "Return the MAX of all dates within all tables within all databases"
Post a Comment