CURSOR in T-SQL

Introduction

Stored Procedure takes database name as input and returns the name of tables and number of rows that belong to each table using Cursor.

/*
     Proc takes Database Name as input parameter
     and return Name of tables and count number of rows of each table
 */
 ALTER Procedure GetTableAndNumRows
 (
     @DataBaseName nVarchar(100)
 )
 AS
 BEGIN
     IF OBJECT_ID('tempdb..#ListTblName') IS NOT NULL
     DROP Table #ListTblName
     CREATE TABLE #ListTblName
     (
         tblName Varchar(100),
         TotalRows Varchar(100)
     )
 DECLARE @tblName Varchar(100);
     DECLARE @totalRowsInTbl Varchar(100);
     DECLARE @Sql_Query nVarchar(Max);  -- hold table data
     -- CURSOR Only takes the SELECT Statement
     -- so we need to assign whole cursor literal to variable and
     -- then execute it which assign SELECT value to CURSOR
     DECLARE @CUR_Query nVarchar(max)
     SET @Cur_Query = N'    DECLARE Tbl_Cursor  CURSOR
                 DYNAMIC FOR
                 SELECT  Name From ' + @DataBaseName + '.sys.Tables'
     EXECUTE SP_EXECUTESQL @Cur_Query


     OPEN Tbl_Cursor;
     FETCH NEXT FROM Tbl_Cursor INTO @tblName;
     WHILE @@FETCH_STATUS = 0
     BEGIN
          SET @Sql_Query = N'SELECT ''' + @tblName + _
          '''  as tblName , Count(*) as TotalRow From ' + @tblName
         INSERT INTO #ListTblName  EXECUTE SP_EXECUTESQL @Sql_Query;
         FETCH NEXT FROM Tbl_Cursor INTO @tblName;
     END
     CLOSE Tbl_Cursor
     DEALLOCATE Tbl_Cursor
     select * from #ListTblName;
 END