Tag Archives: gather

How to use a while loop to iterate through Databases

Say you have code you want executed against every database on a SQL Server instance, you could use SQL Server’s inbuiltĀ sp_MSForEachDB. I’m not a big fan of it though because it is undocumented, so I’d always be concerned Microsoft might decide to kill it with any given patch or service pack update. (I know the likelihood of that is extremely low but I’m a risk adverse kinda guy)

I prefer to use the example below. It may not be the most efficient snippet of code available on the net but it’s good and simple and it’s not going anywhere unless I drop it.

(To execute code against every table in an instance see this post)

SET NOCOUNT ON;

DECLARE @Database TABLE (DbName SYSNAME);
DECLARE @DbName AS SYSNAME;

SET @DbName = '';

INSERT INTO @Database (DbName)
SELECT NAME
FROM sys.databases
WHERE NAME NOT IN (
		'tempdb'
		,'msdb'
		,'model'
		)
	AND state_desc = 'ONLINE'
ORDER BY NAME ASC;

WHILE @DbName IS NOT NULL
BEGIN
	SET @DbName = (
			SELECT MIN(DbName)
			FROM @Database
			WHERE DbName > @DbName
			);

	/*
	PUT CODE HERE
	EXAMPLE PRINT Database Name
	*/
	PRINT @DbName;
END