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