Simple example of WHILE loop to iterate through sys databases

There are multiple methods to loop over  the datbases in sys.databases and execute some code across each database. One example is  sp_msforeachdb  and another example is the CURSOR.

The WHILE loop offers a simple alternative to the above. When combined with a TABLE variable, it’s an effective method of iterating over every database (or a subset) and executing some code.

How to use the WHILE loop

In the example below, a list of databases is inserted to the TABLE variable. The WHILE loop runs through every database and when the code is executed the currentDB is deleted from the TABLE variable.  The WHILE loop moves onto the next database.

DECLARE @dbs TABLE (dbname VARCHAR(50))
DECLARE @currentDB VARCHAR(50)
INSERT INTO @dbs (dbname)
SELECT [Name]
FROM master.sys.databases WHERE state_desc <> 'OFFLINE'
WHILE EXISTS (SELECT dbname FROM @dbs)
BEGIN
     SET @currentDB = (SELECT TOP 1 dbname FROM @dbs ORDER BY dbname)
     PRINT @currentDB
     DELETE FROM @dbs WHERE dbname = @currentDB
END

Read More on SQL Server Performance Tuning

Performance Myth : Replacing a CURSOR with a WHILE LOOP and think you are not processing with a row

SQL Server – Troubleshoot open cursors with sys.dm_exec_cursors

SQL Server - Find last sql statement based on SPID - SQL Server DBA

SQL Performance tuning - Asking the right question