A stored Procedure to Count Numbers of Rows in Table

This a simple Stored procedure to count the total numbers of rows in each table of your Database.
I assume you have Northwind database installed.

The Stored Procedure

IF EXISTS ( SELECT  *
            FROM    sysobjects
            WHERE   id = OBJECT_ID(N'[dbo].[sp_GetRowsCountForAllTables]')
                    AND OBJECTPROPERTY(id, N'IsProcedure') = 1 ) 
    DROP PROCEDURE [dbo].[sp_GetRowsCountForAllTables]
GO

CREATE PROCEDURE sp_GetRowsCountForAllTables
    @DBName VARCHAR(128) = NULL
AS 
    SET nocount ON
    IF @DBName IS NULL 
        SET @DBName = DB_NAME()

    CREATE TABLE #a
        (
          TableName VARCHAR(128)
        , norows INT NULL
        , id INT IDENTITY(1, 1)
        )
 
    DECLARE @id INT
      , @maxID INT
      , @TableName VARCHAR(128)
      , @FKName VARCHAR(128)
      , @cmd NVARCHAR(1000)
      , @rc INT
      , @spcmd VARCHAR(1000)
 
    SET @cmd = 'exec ' + @DBName + '..sp_executesql N''insert #a (TableName) 
   select TABLE_NAME from information_schema.tables
   where TABLE_TYPE = ''''BASE TABLE'''' ''
  '
    EXEC (@cmd)
 
    SELECT  @id = 0
          , @maxID = MAX(id)
    FROM    #a
 
    WHILE @id < @maxID 
        BEGIN
            SELECT  @id = MIN(id)
            FROM    #a
            WHERE   id > @id
  
            SELECT  @TableName = TableName
            FROM    #a
            WHERE   id = @id
  
            SET @cmd = 'exec ' + @DBName
                + '..sp_executesql N''update #a set norows = (select rows from sysindexes where indid in (0,1) and id = object_id('''''
                + @TableName + '''''))'
            SET @cmd = @cmd + ' where #a.id = ' + CONVERT(VARCHAR(10), @id)
                + ''''
  
            EXEC  (@cmd)
            IF @rc <> 0
                OR @@error <> 0 
                BEGIN
                    RAISERROR('failed %s',16,-1,@TableName)
                    RETURN
                END
        END

    SELECT  *
    FROM    #a ORDER BY norows desc

    DROP TABLE #a
GO

The Output:

EXEC sp_GetRowsCountForAllTables