Simple example of WHILE loop to iterate through sys databases

03 December,2014 by Jack Vamvas

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

 


Author: Jack Vamvas (http://www.sqlserver-dba.com)


Share:

Verify your Comment

Previewing your Comment

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

Working...
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.

Working...

Post a comment on Simple example of WHILE loop to iterate through sys databases


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer