WITH subscriber( [publisher] , [publication]) AS (
    SELECT publisher, subscriber
    FROM OPENROWSET('SQLOLEDB','Server=(local);TRUSTED_CONNECTION=YES;','SET FMTONLY OFF; EXEC arizona.dbo.sp_helpsubscriberinfo
    with result sets (( 
        [publisher] nvarchar(128), 
        [subscriber] nvarchar(128), 
        [type] tinyint, 
        [login] nvarchar(128), 
        [password] nvarchar(524), 
        [commit_batch_size] int, 
        [status_batch_size] int, 
        [flush_frequency] int, 
        [frequency_type] int, 
        [frequency_interval] int, 
        [frequency_relative_interval] int, 
        [frequency_recurrence_factor] int, 
        [frequency_subday] int, 
        [frequency_subday_interval] int, 
        [active_start_time_of_day] int, 
        [active_end_time_of_day] int, 
        [active_start_date] int, 
        [active_end_date] int, 
        [retryattempt] int, 
        [retrydelay] int, 
        [description] nvarchar(255), 
        [security_mode] int, 
        [frequency_type2] int, 
        [frequency_interval2] int, 
        [frequency_relative_interval2] int, 
        [frequency_recurrence_factor2] int, 
        [frequency_subday2] int, 
        [frequency_subday_interval2] int, 
        [active_start_time_of_day2] int, 
        [active_end_time_of_day2] int, 
        [active_start_date2] int, 
        [active_end_date2] int 

    ));
    ') [or]
)
, subscription(subscriber, publication, [destination database]) AS (
    SELECT subscriber, publication, [destination database]
    FROM OPENROWSET('SQLOLEDB','Server=(local);TRUSTED_CONNECTION=YES;','SET FMTONLY OFF; EXEC arizona.dbo.sp_helpsubscription
    with result sets (( 
        [subscriber] nvarchar(128), 
        [publication] nvarchar(128), 
        [article] nvarchar(128), 
        [destination database] nvarchar(128), 
        [subscription status] int, 
        [synchronization type] tinyint, 
        [subscription type] int, 
        [full subscription] bit, 
        [subscription name] nvarchar(386), 
        [update mode] int, 
        [distribution job id] varbinary(16), 
        [loopback_detection] bit, 
        [offload_enabled] bit, 
        [offload_server] nvarchar(128), 
        [dts_package_name] nvarchar(128), 
        [dts_package_location] int, 
        [subscriber_security_mode] smallint, 
        [subscriber_login] nvarchar(128), 
        [subscriber_password] varchar(10), 
        [job_login] nvarchar(128), 
        [job_password] varchar(10), 
        [distrib_agent_name] nvarchar(100), 
        [subscriber_type] tinyint, 
        [subscriber_provider] nvarchar(128), 
        [subscriber_datasource] nvarchar(4000), 
        [subscriber_providerstring] nvarchar(4000), 
        [subscriber_location] nvarchar(4000), 
        [subscriber_catalog] nvarchar(128) 
    ));
    ') [or]
)

SELECT DISTINCT
    [ms].[publication]
    ,sub.[publisher]
    ,[ms].[publisher_db]
    ,pub.subscriber
    ,pub.[destination database]
FROM [subscription] pub
    JOIN [subscriber] sub ON sub.publication = pub.subscriber
    JOIN sys.servers s ON s.[data_source] = sub.publication
    LEFT JOIN [distribution].dbo.[MSpublications] [ms] ON ms.publication = pub.publication
WHERE pub.[destination database] = 'gaia'
;