Finding queries with cross schema joins

In a resent case with a development team, we had to look at splitting a database into two or more. This raised the question, are anyone doing queries across schemas (we wanted to split some schemas into their own databases).

This was the script I came up with, it's not an exhaustive list, as it "just" looks into the Query Store:

DECLARE @min_schemas     INT = 2;
DECLARE @days_back       INT = 30;
DECLARE @exclude_schemas NVARCHAR(MAX) = 'sys,guest,INFORMATION_SCHEMA,db_owner,db_accessadmin,db_securityadmin,db_ddladmin,db_backupoperator,db_datareader,db_datawriter,db_denydatareader,db_denydatawriter';

DECLARE @case_block   NVARCHAR(MAX) = N'';
DECLARE @schema_list  NVARCHAR(MAX) = N'';
DECLARE @sql          NVARCHAR(MAX) = N'';

-- Build CASE count block and schema name concat block together
SELECT
    @case_block  += N'
        CASE WHEN qst.query_sql_text LIKE ''%' + s.name + '.%'' THEN 1 ELSE 0 END +',
    @schema_list += N'
        CASE WHEN qst.query_sql_text LIKE ''%' + s.name + '.%'' THEN ''' + s.name + ', '' ELSE '''' END +'
FROM sys.schemas s
WHERE s.name NOT IN (
    SELECT TRIM(value)
    FROM STRING_SPLIT(@exclude_schemas, ',')
)
AND s.schema_id < 16384;

-- Remove trailing +
SET @case_block  = LEFT(@case_block,  LEN(@case_block)  - 1);
SET @schema_list = LEFT(@schema_list, LEN(@schema_list) - 1);

SET @sql = N'
SELECT
    qsq.query_id,
    LEFT(qst.query_sql_text, 300)           AS sql_preview,
    (' + @case_block + N')                  AS schema_hit_count,
    -- trim trailing ", " from the concatenated schema list
    LEFT(
        NULLIF(LTRIM(' + @schema_list + N'), ''''),
        LEN(NULLIF(LTRIM(' + @schema_list + N'), '''')) - 1
    )                                       AS schemas_matched,
    SUM(rs.count_executions)                AS total_executions,
    AVG(rs.avg_duration)                    AS avg_duration_us,
    AVG(rs.avg_logical_io_reads)            AS avg_logical_reads,
    MAX(rs.last_execution_time)             AS last_executed
FROM sys.query_store_query                 qsq
JOIN sys.query_store_query_text            qst ON qsq.query_text_id = qst.query_text_id
JOIN sys.query_store_plan                  qsp ON qsq.query_id      = qsp.query_id
JOIN sys.query_store_runtime_stats         rs  ON qsp.plan_id       = rs.plan_id
WHERE rs.last_execution_time > DATEADD(DAY, -' + CAST(@days_back AS NVARCHAR(10)) + N', GETUTCDATE())
GROUP BY
    qsq.query_id,
    qst.query_sql_text
HAVING
    (' + @case_block + N') >= ' + CAST(@min_schemas AS NVARCHAR(10)) + N'
ORDER BY sql_preview, schema_hit_count DESC, total_executions DESC;
';

-- PRINT @sql;

EXEC sp_executesql @sql,
    N'@exclude_schemas NVARCHAR(MAX)',
    @exclude_schemas = @exclude_schemas;