Find out which SSIS package(s) are executed more than once

We're currently working at a customer who have hundreds of SSIS packages making up their data warehouse. In the past, various developers have worked off and on on the solution, and the customer wanted to make sure that they had an overview of any SSIS packages that are executed in more than 1 other package (basically the general idea is to ensure that long-running packages are run as little as possible).

In order to try and extract that information from SSISDB, rather than manually going through dozens of packages (and most likely overlooking something on more than one occasion), I built the following script.

I know this looks far from optimized, but the script is built te way it is on purpose. The script would take a long time to execute, and thus I broke it up into multiple smaller steps. I also added some DISTINCT clauses in order to get rid of the records.

Hopefully this is helpful for others as well

Code (TSQL):

DECLARE @packages TABLE
(
    package_name sysname
    , executable_name sysname
    , executable_id int
);

INSERT INTO @packages
SELECT
    package_name
    , executable_name
    , executable_id
FROM
(
    SELECT  
        package_name
        , executable_name
        , executable_id
    FROM
        SSISDB.[catalog].[executables]
    WHERE
        executable_name
            /* Find the packages that execute other packages */
        IN (SELECT DISTINCT(REPLACE(Package_name, '.dtsx', '')) FROM SSISDB.[catalog].[executables])
) a
WHERE
    /* Remove the record where the package records itself being executed */
    executable_name != REPLACE(Package_name, '.dtsx', '')
;

DECLARE @packages2 TABLE
(
    executable_name sysname
    , package_name_1 sysname
    , package_name_2 sysname
    , executable_id int
);

INSERT INTO @packages2
SELECT
    DISTINCT
    a.executable_name
    , a.package_name
    , b.package_name
    , MAX(a.executable_id)
FROM
    @packages a
        /* Find where the same package is executed from more than 1 other package */
    JOIN @packages b ON a.executable_name = b.executable_name AND a.package_name != b.package_name
GROUP BY
    a.executable_name
    , a.package_name
    , b.package_name

SELECT
    'SSIS' AS source_system
    , a.*
    , es2.execution_duration / 1000 duration_in_seconds
FROM
    @packages2 a
        /* Get execution time of the last run of the package */
        JOIN (SELECT executable_id, MAX(execution_id) as max_execution FROM SSISDb.catalog.[executable_statistics] GROUP BY executable_id) es
        ON a.executable_id = es.executable_id
        JOIN SSISDb.catalog.[executable_statistics] es2 ON es.executable_id = es2.executable_id AND es.max_execution = es2.execution_id