SQL to get the tables and its row count

Below is the SQL we can use to get the tables and its row count.
CREATE TABLE #counts
(
    table_name varchar(255),
    row_count int
)

EXEC sp_MSForEachTable @command1='INSERT #counts (table_name, row_count) SELECT ''?'', COUNT(*) FROM ?'
SELECT table_name, row_count FROM #counts ORDER BY table_name, row_count DESC

drop table #counts


Alternatively, can use this SQL, it will give the same result:
DECLARE @sql nvarchar(MAX)

SELECT
    @sql = COALESCE(@sql + ' UNION ALL ', '') +
        'SELECT
            ''' + s.name + '.' + t.name + ''' AS ''Table'',
            COUNT(*) AS Count
            FROM ' + QUOTENAME(s.name) + '.' + QUOTENAME(t.name)
    FROM sys.schemas s
    INNER JOIN sys.tables t ON t.schema_id = s.schema_id
    ORDER BY
        s.name,
        t.name

EXEC(@sql)

Reference: here