sqlsql-serversqlresultsetmapping

SQL Query Buffer in result - Show one by one


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.


Solution

  • 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