TYPO3 CMS: Find used page layouts in database

typo3_page_layouts_recursive.sql

###################################################################################
### WARNING: This query uses recursion. This requires MySQL 8+ or MariaDB 10.2+ ###
###################################################################################

# Find the used backend layout for each page, including next level layout from parent pages
WITH RECURSIVE Layout AS (
    SELECT
        uid,
        pid,
        slug,
        title,
        backend_layout,
        backend_layout_next_level,
        hidden,
        deleted,
        endtime
    FROM
         pages
    WHERE
        # This is the starting point, the query goes down the tree
        pid = 0
    UNION ALL
    SELECT
        p.uid,
        p.pid,
        p.slug,
        p.title,
        # Use the current pages (p) layout if set or the parent's next level layout if not
        IF(p.backend_layout != '', p.backend_layout, l.backend_layout_next_level) as backend_layout,
        # Use current pages (p) next level layout, and override parent's, if set
        IF(p.backend_layout_next_level != '', p.backend_layout_next_level, l.backend_layout_next_level) as backend_layout_next_level,
        p.hidden,
        p.deleted,
        p.endtime
    FROM
         pages p
    JOIN
        Layout l ON (p.pid = l.uid)
)
# Select from the accumulated result, making a group by to count usage of each layout
SELECT
    title,
    slug,
    backend_layout,
    count(uid)
FROM
    Layout
WHERE
    hidden = 0
    AND deleted = 0
    AND (endtime = 0 OR endtime > now())
GROUP BY
    backend_layout;