SQL Server Cursor Example – SQL Toolbox

Cursors in SQL Server allow developers to iterate through a collection of values, normally based off a select statement. This is very useful for scripts that must apply to many items at once. More often than not that is iterating through database names to make changes to each database on an instance. Here is an example of how to iterate through all of the databases on an instance using cursors.

DECLARE @name varchar(100)

DECLARE db_cursor CURSOR FOR

SELECT name

FROM MASTER.dbo.sysdatabases

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

OPEN db_cursor

FETCH NEXT FROM db_cursor INTO @name

WHILE @@FETCH_STATUS = 0

BEGIN

print @name;

FETCH NEXT FROM db_cursor INTO @name

END

CLOSE db_cursor

DEALLOCATE db_cursor