Define the Steps for SQL Server Cursor - SQL Server / TSQL Tutorial

Cursors are objects which are provides to us in SQL Server to perform row by row operation.

Here are the steps how do you write a cursor

1-Declare A Cursor by using name of Cursor with a Select statment
2- Open Cursor to populate with output of Select statement
3- Fetch the rows to process them one after one
4- Close the Cursor
5- De-allocate Cursor

We encounter different situations in which we can use cursors. Below are some example in which we can use cursors

Examples:
1-- Kill all connections from a SQL Server Database
2-- Add a new columns such as CreatedBy to all the tables in a Database
3--Enable/Disable all the triggers in a SQL Server Database
4--Generate Scripts/Take backup of All Database on SQL Server one after one
5--Truncate/Delete all the tables from a SQL Server Database


Example Code:
The below code can be used to kill all the processes to a database. We can not rename a database if any process is accessing the database. To rename we have to kill all the processes before we rename the database in SQL Server.

USE MASTER
GO
DECLARE @DatabaseName AS VARCHAR(500)
-->Provide the DataBaseName for which want to Kill all processes.
SET @DatabaseName='TestDB'
DECLARE @Spid INT

--1: Declare Cursor:
DECLARE KillProcessCur CURSOR FOR
  SELECT spid
  FROM   sys.sysprocesses
  WHERE  DB_NAME(dbid) = @DatabaseName

--2: OPEN Cursor
OPEN KillProcessCur

--3: Fetch A record
FETCH Next FROM KillProcessCur INTO @Spid

--Loop Through the Rows one by one
WHILE @@FETCH_STATUS = 0
  BEGIN
      DECLARE @SQL VARCHAR(500)=NULL
      SET @SQL='Kill ' + CAST(@Spid AS VARCHAR(5))

      PRint @SQL
     
      EXEC (@SQL)
      PRINT 'ProcessID =' + CAST(@Spid AS VARCHAR(5))
            + ' killed successfull'
      FETCH Next FROM KillProcessCur INTO @Spid
  END

--4: Close the Cursor
CLOSE KillProcessCur
--5: Deallocate Cursor
DEALLOCATE KillProcessCur