How to List All Pages (With their template names) Within a Website

Question

I'm starting work on a legacy site, that has gotten quite messy. We'd sure like to clean things up. The site has 3914 page entries and with 27 different page templates. I kid you not.

One thing that would really help, and that's if we could list all the pages (by title) WITH THEIR ACCOMPANYING TEMPLATE NAME.

I'm well aware of "list all" type of plugins. All of them use the WordPress core function <?php wp_list_pages( $args ); ?> Unfortunately that function doesn't give much freedom for selecting different elements from within the page.

My tendency is to evaluate that core function, and recreate my own including template name, certainly no easy task.

Anybody been here before? Many thanks.

Update: Based on the suggestion of Howdy_McGee I've been attempting to use WordPress core functions to do this. My code: <?php echo "<ul>"; $query = new WP_Query(); // var_dump($query); while ( $query->have_posts() ) : $query->the_post(); $template_name = get_page_template_slug(); ?> <li>
<?php the_title(); ?> <?php echo " | Template File: ".$template_name; ?> </li> <?php endwhile; ?> </ul>

and that's been a fail. $query returns an empty set. I've been trying to figure out the correct $args.

Edit #2: And then there is Alexey answer. Wow. No really WOW! I'm blown away by that. Never thought about going directly to the database. You've given me a whole new approach to thinking about things on this WordPress stuff. Many thanks!

Answer 1

Not so hard as you expected. Try this:

global $wpdb;

$sql = "SELECT post_title, meta_value
  FROM $wpdb->posts a
  JOIN $wpdb->postmeta b ON a.ID = b.post_id
  WHERE a.post_type = 'page'
  AND a.post_status = 'publish'
  AND b.meta_key = '_wp_page_template'
        ";

$pages = $wpdb->get_results($sql);

echo '<pre>';
print_r($pages);
echo '</pre>';