SQL: Min and Max value for each DATETIME field in a database

The script below outputs a list of each DATETIME and SMALLDATETIME field within a database, along with the minimum and maximum values.

I used this script to check to see if an old legacy database was still being used 1

 
DECLARE @tbl VARCHAR(100)
DECLARE @col VARCHAR(100)
DECLARE @min DATETIME
DECLARE @max DATETIME
DECLARE @sql NVARCHAR(1000)
DECLARE @par NVARCHAR(1000)

CREATE TABLE #Results
( tbl VARCHAR(100), col VARCHAR(100),
  minDate DATETIME, maxDate DATETIME
)

DECLARE csr CURSOR FAST_FORWARD FOR
	SELECT o.name AS tbl, c.name AS col FROM syscolumns c 
	JOIN sysobjects o ON o.id = c.id
	WHERE c.xtype IN (61, 58) AND o.xtype = 'U'
	ORDER BY o.name, c.name
OPEN csr

SET @par = '@maxDate DATETIME OUTPUT, @minDate DATETIME OUTPUT'

FETCH NEXT FROM csr INTO @tbl, @col

WHILE @@FETCH_STATUS = 0
BEGIN
	SET @sql = 'SELECT @maxDate = MAX([' + @col + ']), @minDate = MIN([' + @col + ']) '
	SET @sql = @sql + 'FROM [' + @tbl + ']'
	EXEC sp_executesql @sql, @par, @max OUTPUT, @min OUTPUT;
	INSERT INTO #Results (tbl, col, minDate, maxDate)
	VALUES  ( @tbl, @col, @min, @max )
	FETCH NEXT FROM csr INTO @tbl, @col
END

CLOSE csr
DEALLOCATE csr

SELECT * FROM #Results ORDER BY maxDate DESC
DROP TABLE #Results
  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.