SQLServer : How to count the number of records at each table

This is the sample SQL to count the number of records at each table on current database.

SQL:Table for the result

CREATE TABLE [dbo].[TEST](
  [TableName] VARCHAR(255) NOT NULL,
  [NrOfRecords] [int] NOT NULL
) ON [PRIMARY]

SQL:Count the number of records

DECLARE @TABLE_NAME VARCHAR(255)
DECLARE @SQL        VARCHAR(8000)

DECLARE C_TABLE CURSOR FOR 
SELECT 
  name 
FROM 
  sysobjects 
WHERE 
      OBJECTPROPERTY(id, N'IsUserTable') = 1 
  AND status >= 0 
ORDER BY 
  name

OPEN C_TABLE
FETCH NEXT FROM C_TABLE INTO @TABLE_NAME

WHILE @@FETCH_STATUS = 0
BEGIN
  FETCH NEXT FROM C_TABLE INTO @TABLE_NAME
  PRINT @TABLE_NAME
  SET @SQL = 
      '
        INSERT INTO TEST
        SELECT ''' +
          @TABLE_NAME + ''' AS TableName
          ,COUNT(*) AS NrOfRecords
        FROM ' +
          @TABLE_NAME
      
      EXEC (@SQL)
END

CLOSE C_TABLE