CURSOR Code To Iterate Through Databases In SQL Server

Most of us in SQL world would have come across cursors for some help in functionality here and there in the code. Cursors are used as a part of interpreting the result set on a set of rows. The set of rows called the result set is declared in a cursor for processing the output. There are several instances in the industry where there is a requirement to generate the result set at row level. One possible way is by using cursors. But, let’s check the performance, advantages, and disadvantages of cursors later.

Further, in order to get the result set at row level, we have an approach to iterate through all databases using the cursor as below.

DECLARE db_names CURSOR
FOR
select name from sys.databases
DECLARE @db_name varchar(100)
DECLARE @sql NVARCHAR(max)
OPEN db_names
FETCH NEXT FROM db_names INTO @db_name
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = ' USE '+@db_name+'
GO
<your code here>
GO
'
Print @sql
FETCH NEXT FROM db_names INTO @db_name
END
CLOSE db_names
DEALLOCATE db_names

OUTPUT:

The output gives us a print command, which can be used to execute in a new instance in the SQL Server. If in case, the requirement is to execute the @sql parameter directly, then please comment on the print command and uncomment the Exec command.

Note: Cursors are not the best practice for looping through your SELECT rows. We must always keep in mind to implement them in production thinking their future performance portfolio.

You may also find this interesting: While loop to Iterate through all databases