T-SQL: A Listing Databases Example Using a Cursor

In SQL Server the cursor is a tool that is used to iterate over a result set, or to loop through each row of a result set one row at a time. It may not be the best way to work with a set of data, but if you need to loop row by agonizing row (RBAR) in a T-SQL script then a cursor is one way of doing it.

Note: If you are new to SQL Server and come from an Oracle background, you should know that cursors on SQL Server are different from those on Oracle.

For more information on cursors, also take a look at the free SQL query training provided by Steve Stedman.

Before creating the cursor, we will just start with a simple query that will end up being used in the cursor.

SELECT name, database_id
  FROM sys.databases ;

Now we can take that query and wrap it with a CURSOR:

DECLARE @DatabaseID as INT;
DECLARE @DatabaseName as NVARCHAR(50);

DECLARE @DatabaseCursor as CURSOR;

SET @DatabaseCursor = CURSOR FOR
SELECT name, database_id
  FROM sys.databases ;

OPEN @DatabaseCursor;
FETCH NEXT FROM @DatabaseCursor INTO @DatabaseName, @DatabaseID;

WHILE @@FETCH_STATUS = 0
BEGIN
 PRINT cast(@DatabaseID as VARCHAR (50)) + ' ' + @DatabaseName;
 FETCH NEXT FROM @DatabaseCursor INTO @DatabaseName, @DatabaseID;
END

CLOSE @DatabaseCursor;
DEALLOCATE @DatabaseCursor;

Now, inside of the WHILE loop you could replace the PRINT statement with some else that performs an action on each database. This works similar to how the undocumented sp_foreachdb stored procedure works.

This should give you a quick overview of how to quickly build and use a cursor on Microsoft SQL Server. The example shown was run on SQL Server 2008, and works the same on SQL Server 2005 , SQL Server 2008R2, SQL Server 2012 or SQL Server 2014.

Enjoy!

-Steve Stedman

Related Links

• Free SQL Query Training for the 70-461 course
• Video Training on Using Cursors With SQL Server
• More details on Cursors
• Cursors – Technical debt

More from Stedman Solutions:

Steve and the team at Stedman Solutions are here for all your SQL Server needs.
Contact us today for your free 30 minute consultation..
We are ready to help!