Subscribe to RSS feed  Follow @jackvamvas - Twitter

*Use the Comments section for questions Links


SQL Server DBA Jobs

Simple example of WHILE loop to iterate through sys databases

03 December,2014 by Tom Collins

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))
INSERT INTO @dbs (dbname)
FROM master.sys.databases WHERE state_desc <> 'OFFLINE'
     SET @currentDB = (SELECT TOP 1 dbname FROM @dbs ORDER BY dbname)
     PRINT @currentDB
     DELETE FROM @dbs WHERE dbname = @currentDB

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


Author: Tom Collins (


Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.


Post a comment on Simple example of WHILE loop to iterate through sys databases | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer