Dando EXEC (@Variable) um nome de coluna e concatenação

Você precisará misturar seus 3 SQLs dinâmicos em 1 que retorna 3 colunas, ou manter cada SQL dinâmico, mas misturar seu resultado no final. Existem 2 soluções possíveis a seguir:

Uma solução é usar UNION ALLe PIVOTobter 1 linha com 3 colunas, tudo dentro do SQL dinâmico.

Altere esta parte:

DECLARE @OrphanAuditItems NVARCHAR(MAX)
DECLARE @OrphanAuditAnswers NVARCHAR(MAX) 
DECLARE @OrphanAuditQuestion NVARCHAR(MAX)

--.....

SET @OrphanAuditItems = N'SELECT COUNT(*) FROM [Dynamic].['+ CAST (@TenantId AS NVARCHAR) +'].[AuditItems] 
                                                    WHERE FK_Audit NOT IN (SELECT SystemID FROM [Dynamic].['+ CAST (@TenantId AS NVARCHAR) +'].[Audits])'

SET @OrphanAuditAnswers = N'SELECT COUNT(*) FROM [Dynamic].['+ CAST (@TenantId AS NVARCHAR) +'].[AuditAnswers] 
                                                        WHERE AuditItemId NOT IN (SELECT SystemID FROM [Dynamic].['+ CAST (@TenantId AS NVARCHAR) +'].[AuditItems])'

SET @OrphanAuditQuestion = N'SELECT COUNT(*) FROM [Dynamic].['+ CAST (@TenantId AS NVARCHAR) +'].[AuditQuestion] 
                                                        WHERE AuditId NOT IN (SELECT SystemID FROM [Dynamic].['+ CAST (@TenantId AS NVARCHAR) +'].[Audits])'

EXEC (@OrphanAuditItems)
EXEC(@OrphanAuditAnswers)
EXEC(@OrphanAuditQuestion)

Por esta:

DECLARE @DynamicSQL NVARCHAR(MAX) = N'
    ;WITH PrePivot AS
    (
        SELECT 
            Amount = COUNT(*),
            Type = ''OrphanAuditItems''
        FROM 
            [Dynamic].['+ CAST (@TenantId AS NVARCHAR) +'].[AuditItems] 
        WHERE 
            FK_Audit NOT IN (SELECT SystemID FROM [Dynamic].['+ CAST (@TenantId AS NVARCHAR) +'].[Audits]) 

        UNION ALL

        SELECT 
            Amount = COUNT(*),
            Type = ''OrphanAuditAnswers''
        FROM 
            [Dynamic].['+ CAST (@TenantId AS NVARCHAR) +'].[AuditAnswers] 
        WHERE 
            AuditItemId NOT IN (SELECT SystemID FROM [Dynamic].['+ CAST (@TenantId AS NVARCHAR) +'].[AuditItems])

        UNION ALL

        SELECT 
            Amount = COUNT(*),
            Type = ''OrphanAuditQuestion''
        FROM 
            [Dynamic].['+ CAST (@TenantId AS NVARCHAR) +'].[AuditQuestion] 
        WHERE 
            AuditId NOT IN (SELECT SystemID FROM [Dynamic].['+ CAST (@TenantId AS NVARCHAR) +'].[Audits]) 
    )
    SELECT
        P.OrphanAuditItems,
        P.OrphanAuditAnswers,
        P.OrphanAuditQuestion
    FROM
        PrePivot AS T
        PIVOT (
            MAX(T.Amount) FOR T.Type IN ([OrphanAuditItems], [OrphanAuditAnswers], [OrphanAuditQuestion])
        ) AS P '

-- PRINT (@DynamicSQL)
EXEC (@DynamicSQL)

Certifique-se de usar o PRINTpara validar o SQL resultante antes de executar oEXEC .

Outra solução é recuperar os valores em variáveis, usando sp_executesqlcom OUTPUTparâmetros:

-- Items
DECLARE @OrphanAuditItems INT

DECLARE @DynamicSQL NVARCHAR(MAX) = N'
    SELECT
        @OrphanAuditItems = COUNT(*)
    FROM
        #OrphanResults AS O
        CROSS JOIN [Dynamic].['+ CAST (@TenantId AS NVARCHAR) +'].[AuditItems]
    WHERE 
        FK_Audit NOT IN (SELECT SystemID FROM [Dynamic].['+ CAST (@TenantId AS NVARCHAR) +'].[Audits])'

EXEC sp_executesql 
    @stmt = @DynamicSQL,
    @params = N'@OrphanAuditItems INT OUTPUT',
    @OrphanAuditItems = @OrphanAuditItems OUTPUT


-- Answers
DECLARE @OrphanAuditAnswers INT

SET @DynamicSQL = N'
    SELECT 
        @OrphanAuditAnswers = COUNT(*) 
    FROM 
        [Dynamic].['+ CAST (@TenantId AS NVARCHAR) +'].[AuditAnswers] 
    WHERE 
        AuditItemId NOT IN (SELECT SystemID FROM [Dynamic].['+ CAST (@TenantId AS NVARCHAR) +'].[AuditItems])'

EXEC sp_executesql 
    @stmt = @DynamicSQL,
    @params = N'@OrphanAuditAnswers INT OUTPUT',
    @OrphanAuditAnswers = @OrphanAuditAnswers OUTPUT


-- Questions
DECLARE @OrphanAuditQuestion INT

SET @DynamicSQL = N'
    SELECT 
        @OrphanAuditQuestion = COUNT(*) 
    FROM 
        [Dynamic].['+ CAST (@TenantId AS NVARCHAR) +'].[AuditQuestion] 
    WHERE 
        AuditId NOT IN (SELECT SystemID FROM [Dynamic].['+ CAST (@TenantId AS NVARCHAR) +'].[Audits])'

EXEC sp_executesql 
    @stmt = @DynamicSQL,
    @params = N'@OrphanAuditQuestion INT OUTPUT',
    @OrphanAuditQuestion = @OrphanAuditQuestion OUTPUT

SELECT
    OrphanAuditItems = @OrphanAuditItems,
    OrphanAuditAnswers = @OrphanAuditAnswers,
    OrphanAuditQuestion = @OrphanAuditQuestion

Acho o último um pouco mais flexível e legível do que o primeiro.

Um método é com subconsultas para cada coluna. O exemplo abaixo também usa QUOTENAMEpara incluir identificadores. Pessoalmente, eu nomearia os esquemas de acordo com as regras regulares de nomenclatura do identificador, em vez de usar o TenantId numérico como o nome do esquema.

IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'DetectOrphanDataBeforeUpgrade')
DROP PROCEDURE [dbo].[DetectOrphanDataBeforeUpgrade]
GO

CREATE PROCEDURE [dbo].[DetectOrphanDataBeforeUpgrade]

@TenantId   INT = NULL

AS
BEGIN

DECLARE @OrphanAuditData NVARCHAR(MAX)

------------------------------------------------------------------------------------------------
/* Throw an error if the TenantId is NULL or Invalid.               */
------------------------------------------------------------------------------------------------
IF @TenantId IS NULL OR @TenantId NOT IN (SELECT Tenants FROM [Application].[dbo].[TenantIdNumber])
BEGIN
    THROW 51000, '@TenantId is invalid because it is NULL or does not exist.', 1;
END


------------------------------------------------------------------------------------------------
/* Checks for Orphan records related to the Audits table                */
------------------------------------------------------------------------------------------------
SET @OrphanAuditData = N'SELECT
    (SELECT COUNT(*) 
        FROM [Dynamic].' + QUOTENAME(CAST(@TenantId AS NVARCHAR)) + '.[AuditItems] 
        WHERE FK_Audit NOT IN (SELECT SystemID FROM [Dynamic].' + QUOTENAME(CAST(@TenantId AS NVARCHAR)) + '.[Audits])) AS OrphanAuditItems
'
+ N'    ,(SELECT COUNT(*)
        FROM [Dynamic].'+ QUOTENAME(CAST(@TenantId AS NVARCHAR)) + '.[AuditAnswers] 
        WHERE AuditItemId NOT IN (SELECT SystemID FROM [Dynamic].' + QUOTENAME(CAST(@TenantId AS NVARCHAR)) + '.[AuditItems])) AS OrphanAuditAnswers
'
+ N'    ,(SELECT COUNT(*)
        FROM [Dynamic].'+ QUOTENAME(CAST(@TenantId AS NVARCHAR)) + '.[AuditQuestion] 
        WHERE AuditId NOT IN (SELECT SystemID FROM [Dynamic].'+ QUOTENAME(CAST(@TenantId AS NVARCHAR)) + '].[Audits])) AS OrphanAuditQuestion;'

EXEC (@OrphanAuditData);

END;
GO

Eu pessoalmente inseriria todos os dados em uma tabela de retenção na forma de ([COUNT] INT NOT NULL, [TableName] NVARCHAR(MAX) NOT NULL) conforme abaixo. Você pode então usar PIVOT conforme abaixo para obter os valores para cada tabela.

Como um aparte, observe também o uso da função QUOTENAME - isso garantirá que seu código não seja vulnerável à injeção de SQL se a tabela for passada como uma variável (eu procuraria simplesmente fornecer uma lista de nomes de tabelas e tê-la percorra todos eles dinamicamente pessoalmente - feliz em escrever isso e fornecer uma resposta, se desejar).

CREATE TABLE #Datas (ID INT);

INSERT INTO #Datas
(
    ID
)
VALUES
( 1  -- ID - int
    )
    GO

DECLARE @SQL1 NVARCHAR(MAX) = 'SELECT COUNT(1), ''TableName1'' FROM ' + QUOTENAME('#Datas') + ';'
DECLARE @SQL2 NVARCHAR(MAX) = 'SELECT COUNT(1), ''TableName2'' FROM ' + QUOTENAME('#Datas') + ';'
DECLARE @SQL3 NVARCHAR(MAX) = 'SELECT COUNT(1), ''TableName3'' FROM ' + QUOTENAME('#Datas') + ';'

CREATE TABLE #Counts (Cnt INT, Tbl NVARCHAR(MAX));

INSERT INTO #Counts
EXEC (@SQL1);

INSERT INTO #Counts
EXEC (@SQL2);

INSERT INTO #Counts
EXEC (@SQL3);

SELECT * FROM #Counts

PIVOT (SUM(Cnt) FOR Tbl IN ([TableName1],[TableName2],[TableName3])) AS d

DROP TABLE #Datas;
DROP TABLE #Counts

reprodução do dbfiddle

Se você estiver usando o SQL Server 2012 ou posterior, também poderá usar WITH RESULT SETScom seu EXEC:

exec (N'select 1') 
WITH RESULT SETS
(
    (
        ID int
    )
)