If you're trying to generate a dynamic SQL command to return rowcounts for every table, this would work.
DECLARE @sql NVARCHAR(MAX) = STUFF((
        SELECT CONCAT(' UNION ALL SELECT TableName = ''', TableName, ''', [RowCount] = COUNT(*) FROM ', TableName)
        FROM (
            SELECT TableName = CONCAT(QUOTENAME(s.name), '.', QUOTENAME(t.name))
            FROM sys.tables t
            INNER JOIN sys.schemas s on t.schema_id=s.schema_id
        ) TableList
        ORDER BY TableName
        FOR XML PATH(''), TYPE
    ).value('.','nvarchar(max)'), 1, 11, '');

EXEC (@sql);
Another way to do this without dynamic SQL is to get the table rowcounts from sys.partitions.
SELECT TableName = CONCAT(OBJECT_SCHEMA_NAME(object_id), '.', OBJECT_NAME(object_id)), [RowCount] = SUM(rows)
FROM sys.partitions
WHERE index_id < 2 AND object_id IN (SELECT object_id FROM sys.tables)
GROUP BY OBJECT_ID
ORDER BY TableName