DBCC
-
We have 59 database, I want to run DBCC for all the database and want to schedule this job.
Right now I am using this for 5 of my database
use Schm_1 DBCC CHECKDB GO
use Schm_2 DBCC CHECKDB GO
use Schm_3 DBCC CHECKDB GO
use Schm_4 DBCC CHECKDB GO
use Schm_5 DBCC CHECKDB GO
I have to write manually or is there other way.
Nita
-
Something like this might work (untested)
EXEC
dbo.SP_MSForEachDB 'DBCC CHECKDB'
-
Now with testing
EXEC
dbo.SP_MSForEachDB 'USE ?; DBCC CHECKDB'
-
Thanks this works great, but it also runs for Tempdb, PUBS and Northiwind database which I don't want to run for this DB. How to illiminate this DB.
-
... and also if this is a production server, I don't see why Pubs and Northwind are installed
. -
Well you could wrap an if around the dbcc command >>> 'Use ?; IF ''?'' not in ('tempdb', 'Pubs', 'NorthWind') begin DBCC CHECKDB END
You can pass pretty much any valid sql statement and it will be executed in all the databases of the server. The trick is to know how to work with the ? variable (current dbname) and passing the quotes accordingly.
-
You can loop through the DB and do the same as well.
Use Master
go
Declare @s_DBName nvarchar(128)
DECLARE DB_cursor CURSOR FOR
select name from master.dbo.sysdatabases where name not in ('tempdb', 'Pubs', 'NorthWind')
OPEN DB_cursor
FETCH NEXT FROM DB_cursor INTO @s_DBName
WHILE @@FETCH_STATUS = 0
BEGIN
DBCC CHECKDB (@s_DBName)
FETCH NEXT FROM DB_cursor INTO @s_DBName
END
CLOSE DB_cursor
DEALLOCATE DB_cursor
Thanks
Sreejith
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply