T-SQL:
Use Distribution
go
DECLARE @PublicationName varchar(100)
SET @PublicationName = ''
SELECT t5.srvname as 'Publication Server', t1.publisher_db, t1.publication,
CASE t1.publication_type
WHEN 0 THEN 'Transactional'
WHEN 1 THEN 'Snapshot'
WHEN 2 THEN 'Merge'
END AS 'publication_type',
t2.source_owner, t2.article, t3.name as 'Distribution Agent SQL Job',
t4.profile_name as 'Distribution Agent Profile',
t6.srvname as 'Subscription Server', t3.subscriber_db, t3.subscriber_login,
CASE t3.subscription_type
WHEN 0 THEN 'Push'
WHEN 1 THEN 'Pull'
WHEN 2 THEN 'Anonymous'
END AS 'subscription_type',
CASE t3.subscriber_security_mode
WHEN 0 THEN 'Microsoft SQL Server Authentication'
WHEN 1 THEN 'Microsoft Windows Authentication'
END AS 'subscriber_security_mode'
FROM [distribution].[dbo].[MSpublications] t1
INNER JOIN [distribution].[dbo].[MSarticles] t2 on t1.publication_id = t2.publication_id
INNER JOIN [distribution].[dbo].[MSdistribution_agents] t3 on t1.publication = t3.publication
INNER JOIN [MSDB].[dbo].[MSagent_profiles] t4 on t3.profile_id = t4.profile_id
INNER JOIN master..sysservers t5 ON t1.publisher_id = t5.srvid
INNER JOIN master..sysservers t6 ON t3.subscriber_id = t6.srvid
WHERE t1.publication = @PublicationName
ORDER BY t1.publisher_db asc, t2.article asc
If no Subscribers exist but Publication does:
Use Distribution
go
DECLARE @PublicationName varchar(100)
SET @PublicationName = ''
SELECT t5.srvname as 'Publication Server', t1.publisher_db, t1.publication,
CASE t1.publication_type
WHEN 0 THEN 'Transactional'
WHEN 1 THEN 'Snapshot'
WHEN 2 THEN 'Merge'
END AS 'publication_type',
t2.source_owner, t2.article, t3.name as 'Distribution Agent SQL Job',
t4.profile_name as 'Distribution Agent Profile',
t6.srvname as 'Subscription Server', t3.subscriber_db, t3.subscriber_login,
CASE t3.subscription_type
WHEN 0 THEN 'Push'
WHEN 1 THEN 'Pull'
WHEN 2 THEN 'Anonymous'
END AS 'subscription_type',
CASE t3.subscriber_security_mode
WHEN 0 THEN 'Microsoft SQL Server Authentication'
WHEN 1 THEN 'Microsoft Windows Authentication'
END AS 'subscriber_security_mode'
FROM [distribution].[dbo].[MSpublications] t1
INNER JOIN [distribution].[dbo].[MSarticles] t2 on t1.publication_id = t2.publication_id
LEFT JOIN [distribution].[dbo].[MSdistribution_agents] t3 on t1.publication = t3.publication
LEFT JOIN [MSDB].[dbo].[MSagent_profiles] t4 on t3.profile_id = t4.profile_id
LEFT JOIN master..sysservers t5 ON t1.publisher_id = t5.srvid
LEFT JOIN master..sysservers t6 ON t3.subscriber_id = t6.srvid
WHERE t1.publication = @PublicationName
ORDER BY t1.publisher_db asc, t2.article asc
Note:
Code assumes Distribution DB and Publication DB are on the same MSSQL Server.
/*
use distribution
go
select * from [dbo].[MSpublications]
select * from [dbo].[MSpublisher_databases]
select * from [dbo].[MSsubscriptions]
select * from [dbo].[MSsubscriber_info]
select * from [dbo].[MSsubscriber_schedule]
select * from [dbo].[MSarticles]
select * from [dbo].[MSdistribution_agents]
select * from [dbo].[MSdistribution_history]
select * from [dbo].[MSpublicationthresholds]
select * from [dbo].[MSpublication_access]
use msdb
go
select * from [dbo].[MSagent_profiles]
select * from [dbo].[MSagent_parameters]
select * from [dbo].[MSagentparameterlist]
select * from [dbo].[MSdistributor]
select * from [dbo].[MSdistributiondbs]
select * from [dbo].[MSdistpublishers]
select * from [dbo].[MSreplmonthresholdmetrics]
*/
|
|
|
|
|
|