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