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?
SET @databasename =
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, email@example.com
Microsoft is conducting an online survey to understand your opinion of the Technet Web site. If you choose to participate, the online survey will be presented to you when you leave the Technet Web site.