The following SQL script will count all records in all tables in a database.​

DECLARE @TableRowCounts TABLE ([TableName] VARCHAR(128), [RowCount] INT) ;

INSERT INTO @TableRowCounts ([TableName], [RowCount])
EXEC sp_MSforeachtable 'SELECT ''?'' [TableName], COUNT(*) [RowCount] FROM ?' ;

SELECT
    [TableName], [RowCount]
FROM
    @TableRowCounts
WHERE
   [RowCount] > 0
ORDER BY
    [TableName]

SELECT

    SUM([RowCount]) as 'Total Records'
FROM
    @TableRowCounts