Cursor in SQL Server (with example)

What is a Cursor?

Cursor is a variable in SQL Server Database which is used for row-by row operations. It indicates the current position in the result set so that it is known as Cursor.

Script for taking backup of all databases by using cursor.

DECLARE @dbName VARCHAR(50) --DATABASE NAME

DECLARE @path VARCHAR(500)  --BACKUP PATH

DECLARE @fileName VARCHAR(100) --FILE NAME FOR BACKUP

DECLARE @fileDate VARCHAR(100) --FILE DATE FOR NAME WITH FILE NAME

SET @path = 'C:\Backup\'       --BACKUP PATH

SELECT @fileDate = CONVERT(VARCHAR, GETDATE(),105)

DECLARE dbName_cursor CURSOR FOR   --DECLARATION AND INITILIZING CURSOR

SELECT [name] FROM [master].[dbo].[sysdatabases]

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

OPEN dbName_cursor        --OPEN CURSOR

FETCH NEXT FROM dbName_cursor INTO @dbName

WHILE @@FETCH_STATUS = 0

BEGIN

    SET @fileName = @path+@dbName+'_'+@fileDate+'.bak'

    BACKUP DATABASE @dbName TO DISK = @fileName

   

    FETCH NEXT FROM dbName_cursor INTO @dbName

END

CLOSE dbName_cursor      --CLOSING CURSOR

DEALLOCATE dbName_cursor --DEALLOCATION MEMORY