Jeremy Carter

I use this little script all the time for a variety of reasons. I am a firm believer that a database should have its consistency checked at least once a week. This lets me verify that the best practice is being followed and that dbcc checkdb has recently ran successfully.

The second use case is when a database is already corrupted. Once I gain access to the db I can check to see the last time it was checked successfully. This helps in planning the proper course of action and gives me some indication of how long the corruption may have existed.

Lastly the script also checks to see if data purity checks are enabled. Prior to SQL 2005 data types were not always strictly enforced. The holes were closed and this new check was added. However for any database that was upgraded from earlier versions the data purity check is not enabled by default. To enable the check for upgraded databases dbcc checkdb needs to be ran with the WITH DATA_PURITY option. Once this option is used the first time, it will forever after be ran automatically.

DECLARE @name VARCHAR(256)</code>

CREATE TABLE #dbinfo
(ParentObject varchar(100),
Object varchar(100),
Field varchar(100),
Value varchar(100))

CREATE TABLE #dbinforesults
(dbname varchar(256),
LastRanDate datetime,
Status varchar(100),
DataPurityCheckEnabled varchar(3))

DECLARE db_cursor CURSOR FOR
SELECT name FROM sys.databases
WHERE state_desc='ONLINE'

OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @name

WHILE @@FETCH_STATUS = 0
BEGIN

INSERT INTO #dbinfo
EXEC('DBCC DBINFO (['+@name+']) WITH TABLERESULTS')

INSERT INTO #dbinforesults (dbname, LastRanDate, Status)
(SELECT DISTINCT @name as dbname, Value as LastRanDate,
CASE
WHEN Value = '1900-01-01 00:00:00.000' THEN CAST('NeverRan' AS VARCHAR)
WHEN DATEDIFF(d, Value, GETDATE()) > 14 THEN CAST('NotCurrent' AS VARCHAR)
ELSE CAST('Current' AS VARCHAR)
END AS Status
FROM #dbinfo
WHERE Field='dbi_dbccLastKnownGood')

UPDATE #dbinforesults SET DataPurityCheckEnabled=(SELECT
CASE
WHEN @name='master' OR @name='model' THEN 'N/A'
WHEN Value=0 THEN 'No'
WHEN Value=2 THEN 'Yes'
END as DataPurityEnabled
FROM #dbinfo
WHERE Field='dbi_dbccFlags')
WHERE dbname=@name

TRUNCATE TABLE #dbinfo

FETCH NEXT FROM db_cursor INTO @name

END

CLOSE db_cursor
DEALLOCATE db_cursor

DROP TABLE #dbinfo

SELECT * FROM #dbinforesults

DROP TABLE #dbinforesults

Here are what the results will look like.

-Jeremy