WHILE Loop To Iterate Through All Databases In SQL Server

SQL WHILE loops are used to repeatedly execute the SQL commands until certain desired conditions are met. Many DBAs are well-versed with using the WHILE loops as they seem to be one of the biggest integral of their DBA life. Additionally, the common reason behind using the WHILE loop is to iterate through the condition until the condition becomes false. The looping conditions can be controlled with BREAK and CONTINUE commands, and this can be applied only on the WHILE loop.

General WHILE loop Syntax:

WHILE Condition
BEGIN
<'your SQL Command here'>
END

There are two other ways to iterate through all databases in a server, one by using sp_msforeachdb and another by using CURSORS. But if you notice carefully the insights, the WHILE loops are going to perform better than CURSORS.

Let’s check the WHILE loop code to iterate through all the databases:

From the code, if you notice that we are creating a temp table and inserting all the databases from sys.databases. Once the insertion is completed into the temp table, the databases in the temp table are iterated based @@rowcount system variable.

SET NOCOUNT ON
Declare
@cnt int ,
@i int=1,
@dbname varchar(500),
@cmd nvarchar(max)

create table #DBIterate
(
id int identity (1,1),
name varchar(500)
)

insert into #DBIterate
select name from sys.databases

set @cnt=@@rowcount
While (@i<=@cnt)
begin
select @dbname='use ' + name from #DBIterate
where id=@i
set @cmd='your code here'
print (@dbname)
print (@cmd)

set @i=@i+1
end
drop table #DBIterate