Drupal 8 - Some SQL Queries

queries.sql

- List nodes w/o aliases
    SELECT nid, type FROM node WHERE nid NOT IN (
            SELECT SUBSTR(source, 7) FROM url_alias WHERE source LIKE '/node/%'
    );

- List nodes w/o aliases and group by content type

    SELECT nid, type FROM node WHERE nid NOT IN (
            SELECT SUBSTR(source, 7) FROM url_alias WHERE source LIKE '/node/%'
    ) GROUP BY type;
    
- List nodes w/o aliases along with whether or not “Generate automatic alias” is checked
    SELECT node.nid, node.type, key_value.name, key_value.`value` FROM node
            JOIN key_valueON node.nid = key_value.name
            WHERE nid NOT IN (SELECT SUBSTR(source, 7) FROM url_alias WHERE source LIKE '/node/%')
            AND key_value.value = 'i:0;';


- List unused paragraph types
    SELECT DISTINCT SUBSTR(name, 28) FROM config
            WHERE name LIKE 'paragraphs.paragraphs_type.%'
            AND SUBSTR(name, 28) NOT IN (SELECT DISTINCT type FROM paragraphs_item);