Tuesday, January 13, 2009

SQL Check DB

/*
This is the routine I use for running check DB. It simply loops through the master miles and runs check DB. There is an optional parameter to suppress info messages.
*/

USE [master]
GO

DECLARE @tDatabases TABLE
( databaseId int
);

INSERT INTO @tDatabases ( databaseId )
SELECT DISTINCT s_mf.database_id
FROM sys.databases s_mf
WHERE s_mf.state = 0 -- ONLINE
AND has_dbaccess(db_name(s_mf.database_id)) = 1 -- Only look at databases to which we have access
AND NOT [NAME] IN ('tempdb', 'master', 'model', 'msdb' )--eliminate temp db
;

DECLARE cursor_usage CURSOR FOR SELECT databaseId FROM @tDatabases;

DECLARE @dbId INT;

OPEN cursor_usage;
FETCH NEXT FROM cursor_usage INTO @dbId;

WHILE @@FETCH_STATUS = 0
BEGIN
PRINT db_name(@dbId);
DECLARE @dbname nvarchar(50)
SET @dbname = db_name(@dbId);
DBCC CHECKDB (@dbName);

FETCH NEXT FROM cursor_usage INTO @dbId;
END;

CLOSE cursor_usage;
DEALLOCATE cursor_usage;

No comments: