Ramblings of a DBA

Tara Kizer
posts - 166, comments - 837, trackbacks - 75

My Links

Advertisement

News

Subscribe
Search this Blog

Archives

Post Categories

Work

DBCC CHECKDB stored procedure

EDIT: This stored procedure has been updated.

Here's a stored procedure that will check the integrity of each of the databases.  I've got it scheduled to run every night on each of our servers.  I recommend putting it into an Admin database (hey just name it Admin!) rather than in master.

CREATE    PROC isp_DBCC_CHECKDB
AS

SET NOCOUNT ON

DECLARE @dbid INT
DECLARE @DBName SYSNAME
DECLARE @SQL NVARCHAR(4000)

SET @dbid = 0

WHILE @dbid < (SELECT MAX(dbid) FROM master.dbo.sysdatabases)
BEGIN

 SELECT TOP 1 @dbid = dbid, @DBName = name
 FROM master.dbo.sysdatabases
 WHERE dbid > @dbid
 ORDER BY dbid

 SET @SQL = 'DBCC CHECKDB(' + @DBName + ')'

 EXEC sp_executesql @statement = @SQL

END

RETURN 0


GO

Print | posted on Wednesday, June 16, 2004 2:36 PM | Filed Under [ SQL Server - Database Administration ]

Feedback

Gravatar

# re: DBCC CHECKDB stored procedure

If you want to save few lines of code you can use the following.
sp_MSforeachdb 'DBCC CHECKDB(?)'

Disclaimer : It is an undocumented feature.
6/16/2004 4:03 PM | Satish Balusa
Gravatar

# re: DBCC CHECKDB stored procedure

Yes thanks, I am aware of sp_MSforeachdb.
6/17/2004 9:05 AM | Tara
Gravatar

# Links of Interest

Links of Interest
6/17/2004 8:46 PM | Enjoy Every Sandwich
Gravatar

# SQL Server: Database Maintenance Routine

7/29/2004 12:37 PM | Khurram Aziz
Gravatar

# re: DBCC CHECKDB stored procedure

Should probably change it to

'DBCC CHECKDB([' + @DBName + '])'

since some people have -,_ in their database names :)
8/5/2004 7:18 AM | Lyle Dodge
Gravatar

# Database maintenance routines

9/23/2004 6:37 PM | Ramblings of a DBA
Gravatar

# re: DBCC CHECKDB stored procedure

Is it possible to Exclude Offline databases with this?
6/14/2006 8:19 AM | Emma Greenwood
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET