I have a sql script as follow :
declare db_list cursor for
select name From sys.databases
open db_list
declare @var varchar(MAX)
fetch next from db_list into @var
print @var
while (@@FETCH_STATUS = 0)
BEGIN
EXEC('use '+@var)
print 'Checking database '+@var
print '---------------------------------------------'
dbcc checkdb
fetch next from db_list into @var
END
close db_list
deallocate db_list
I wish to get the result one by one. For instance, when @var is set to 'master'. It should show :
Checking database master
and then it should show 'dbcc checkdb' result for master.
Instead the result hangs for undefined time and then suddenly displays all results for all databases. I am using MS SQL Server 2008 for this.
As Dan Guzman said, you can use RAISERROR
.
However, you cannot EXEC('use '+@var)
and later use dbcc checkdb
. These two statements are executed in different batches, so the dbcc is always executed on your current connection. Try the following, it worked for me (sql server 2012/2014). I also changed the cursor a bit to exclude Microsoft owned tables (Master, Model etc). If you do want those checked, you know what to do :P.
DECLARE @text NVARCHAR(100) = 'Checking database '
, @ErrorText NVARCHAR(100)
, @var varchar(MAX)
declare db_list cursor FOR
select name From sys.databases
WHERE owner_sid <> 0x01
open db_list
fetch next from db_list into @var
print @var
while (@@FETCH_STATUS = 0)
BEGIN
SET @ErrorText = @text + @var
RAISERROR(@ErrorText, 0, 0) WITH NOWAIT
RAISERROR('---------------------------------------------', 0, 0) WITH NOWAIT
EXEC('use ' + @var + '; dbcc checkdb')
fetch next from db_list into @var
END
close db_list
deallocate db_list