91 lines
3.2 KiB
SQL
91 lines
3.2 KiB
SQL
|
|
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'
|
|
;
|
|
|