SQL Query Buffer in result - Show one by one

Question

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.

What is the main problem? You trying to call dbcc checkdb not for current database or print results? — Evgeny, Jun 28 '17 at 13:32

Dan Guzman · Answer 1 · 2017-06-28T15:38:14.877

1

To avoid buffering results, you can use RAISERROR...WITH NOWAIT with severity 0 to generate an info message:

RAISERROR ('Checking database %s', 0, 0, @var) WITH NOWAIT;

If there are preceding buffered results, such as from DBCC output, those will also be flushed by the RAISERROR...WITH NOWAIT.

edited Jun 28 '17 at 15:38

answered Jun 28 '17 at 13:14

Dan Guzman

43,250
3
46
71

Will it show dbcc result for one database and then jump to another? instead of showing all dbcc checkdb result at once. ??? – Sumit Singh Jun 28 '17 at 13:31

score 1 · Answer 2 · answered Jun 28 '17 at 13:25

1

If you want to use it not for current database, try to do this:

exec('dbcc checkdb (' + @var + ', NOINDEX)')

answered Jun 28 '17 at 13:25

Evgeny

597
2
7
16

Thanks but that gives output all at once. Still thanks for your valuable answer. – Sumit Singh Jul 20 '18 at 06:17

HoneyBadger · Accepted Answer · 2017-06-28T13:24:35.200

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

Thanks for your answer. I refined the answer to following code as your code made sql skip all databases. Since owner_sid is same for all in my case. — Sumit Singh, Jun 29 '17 at 06:39

SQL Query Buffer in result - Show one by one

3 Answers3