dbcc msforeachDB

Question

Post reply

dbcc msforeachDB

sunshine-587009

SSCrazy Eights

Points: 8981
More actions
June 13, 2008 at 1:43 pm

#121968

I am running this
declare @database_name varchar(20)
exec sp_msforeachdb 'dbcc checkdb (@database_name)'
and receiving this error.
Server: Msg 137, Level 15, State 2, Line 1
Must declare the variable '@database_name'.
What am I doing wrong?
¤ §unshine ¤

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

JamieX Hall of Fame Points: 3763 More actions · Answer 1

change to

EXEC sp_MSforeachdb @command1="DBCC CHECKDB ('?')"

Wilfred van Dijk SSCrazy Eights Points: 9003 More actions · Answer 2

The dynamic part in sp_msforeachdb and ms_foreachtable is not a variable, but a questionmark. In the actual statement, MSSQL will replace the questionmark in your code by the databasename

Wilfred
The best things in life are the simple things

sunshine-587009 SSCrazy Eights Points: 8981 More actions · Answer 3

Is it possible to set this up with multiple statements?

I'd like to set to single user mode, run a repair and then reset to multi user. I'll be trying a few things on my end in the meantime.

¤ §unshine ¤

sunshine-587009 SSCrazy Eights Points: 8981 More actions · Answer 4

I try this...

DECLARE @cmd1 varchar(100)

SET @cmd1 = 'ALTER ? SET SINGLE_USER'

DECLARE @cmd2 varchar(100)

SET @cmd2 = 'DBCC CHECKDB (''[?]'',REBUILD_REPAIR)'

DECLARE @cmd3 varchar(100)

SET @cmd3 = 'ALTER ? SET MULTI_USER'

EXEC sp_msforeachdb @command1 = @cmd1,

@command2=@cmd2,

@command3=@cmd3

And get these errors...

erver: Msg 170, Level 15, State 1, Line 1

Line 1: Incorrect syntax near 'model'.

Server: Msg 2560, Level 16, State 14, Line 1

Parameter 2 is incorrect for this DBCC statement.

Server: Msg 170, Level 15, State 1, Line 1

Line 1: Incorrect syntax near 'model'.

System db's should not be included but I will figure that one out later.. any help appreciated!

¤ §unshine ¤

JamieX Hall of Fame Points: 3763 More actions · Answer 5

try this (you have some syntax incorrect)

DECLARE @cmd1 varchar(100)

SET @cmd1 = 'ALTER DATABASE [?] SET SINGLE_USER'

DECLARE @cmd2 varchar(100)

SET @cmd2 = 'dbcc checkdb('?',repair_rebuild) '

DECLARE @cmd3 varchar(100)

SET @cmd3 = 'ALTER DATABASE [?] SET MULTI_USER'

//EDIT

forgot to add this link. This is how you exclude the system databases.

Just put your code between the begin and end in the IF block.

http://www.sqlservercentral.com/Forums/Topic438415-266-1.aspx

MannySingh SSChampion Points: 12465 More actions · Answer 6

Try this cursor option. I know coding with sp_msforeach db is possible. but this is another way to achieve the same results..

DEclare @sqlstm varchar(200)

Declare @dbname sysname

Declare rolldbname cursor for

Select name from master.sys.sysdatabases where dbid>4

open rolldbname

fetch next from rolldbname into @dbname

While @@fetch_status=0

BEGIN

SET @sqlstm='

ALTER Database '+@dbname+' SET SINGLE_USER

WITH ROLLBACK IMMEDIATE

DBCC CHECKDB ('+@dbname+') WITH PHYSICAL_ONLY

ALTER DATABASE '+@dbname+ ' SET MULTI_USER'

EXEC (@sqlstm)

fetch next from rolldbname into @dbname

END

close rolldbname

deallocate rolldbname

Maninder
www.dbanation.com

sunshine-587009 SSCrazy Eights Points: 8981 More actions · Answer 7

I tried the first one and there is still some kind of syntax error. I tried the 2nd one and it seems to work. I would prefer no cursor, but I believe that is what msforeachdb does.

thank you so much for your help!

¤ §unshine ¤

MannySingh SSChampion Points: 12465 More actions · Answer 8

yes sp_msforeachdb does the cursor thing check this:

use master

sp_helptext sp_msforeachdb

Maninder
www.dbanation.com