noalt.php

<?php

use Drupal\Core\Entity\Sql\SqlEntityStorageInterface;
use Drupal\field\Entity\FieldStorageConfig;

// run this with drush scr
$ids = \Drupal::entityQuery('field_storage_config')
  ->condition('settings.target_type', 'media')
  ->execute();
$fscs = FieldStorageConfig::loadMultiple($ids);
$all_results = [];
foreach ($fscs as $fsc) {
  $storage = \Drupal::entityTypeManager()->getStorage($fsc->getTargetEntityTypeId());
  assert($storage instanceof SqlEntityStorageInterface);
  $mapping = $storage->getTableMapping();
  $table = $mapping->getFieldTableName($fsc->getName());
  $column = $mapping->getFieldColumnName($fsc, 'target_id');
  $select = \Drupal::database()->select('media__field_media_image', 'm');
  $select->innerJoin($table, 'f', "$column = m.entity_id AND f.langcode = m.langcode");
  $select->innerJoin('file_managed', 'fm', 'fm.fid = m.field_media_image_target_id');
  $select->where("COALESCE(field_media_image_alt,'') = ''");
  $select->addField('m', 'entity_id');
  $select->addField('fm', 'uri');
  $result = $select->execute()->fetchAll(\PDO::FETCH_ASSOC);
  $all_results = array_merge($all_results, array_map(fn ($x) => implode("\t", $x), $result));
}
$all_results = array_unique($all_results);
sort($all_results, SORT_NUMERIC);
file_put_contents('noalt.txt', implode("\n", $all_results));