Query with a WHILE loop


  • DECLARE @databasename AS NVARCHAR(128);

    SET @databasename = (SELECT MIN(name) FROM sys.databases

    WHERE name NOT IN    ('master', 'model', 'msdb', 'tempdb'));

     WHILE @databasename IS NOT NULL BEGIN

       PRINT @databasename;

        SET @databasename = (SELECT MIN(name) FROM sys.databases

            WHERE name NOT IN     ('master', 'model', 'msdb', 'tempdb')

            AND name > @databasename); END






    I have question related to the above SQL statement, forgive me as I am unfamiliar with WHILE loops.

    I understand that the query above finds the minimum database name, prints the value then finds the next database greater than the minimum.

    What happens when it loops occurs,  I know it prints the database name of the one it has found.  In the example above it will be B.  Will the @databasename be then set to B and the following query will be run so the @databasename will then be set to C?

     SELECT MIN(name) FROM sys.databases

            WHERE name NOT IN     ('master', 'model', 'msdb', 'tempdb')

            AND name > @databasename);

    Sunday, July 13, 2014 9:48 AM


  • Yes. You run this query:

    SET  @databasename =
       (SELECT MIN(name)
        FROM   sys.databases
        WHERE  name NOT IN    ('master','model', 'msdb', 'tempdb'));

    This gives you the first user database, alphabetically according to your system database. The WHERE clause filters out the system databases.

    Then in the WHILE loop you do the same, with one condition added:

         AND name > @databasename

    So this gives the next user database alphabetically. But if there is no further database, the query returns NULL, and the condition in the WHILE statement will evaluate to FALSE, and the loop will terminate.

    Erland Sommarskog, SQL Server MVP,
    Sunday, July 13, 2014 10:30 AM