USE [distribution];

SET NOCOUNT ON;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

SELECT	
	[state] =
		CASE	sub.[status]
			WHEN 0 THEN '[]' 
			WHEN 1 THEN '||'
			WHEN 2 THEN '>>'
		END
,	[publication_db_name] = pub.publisher_db
,	[publication_name] = pub.publication
,	[publication_article_name] = QUOTENAME(art.source_owner) + N'.' + QUOTENAME(art.source_object) 
,	sub.subscriber_id
,	sub.subscriber_db
,	[subscription_type] = 
		CASE	sub.[subscription_type]
			WHEN 0 THEN 'Push'
			WHEN 1 THEN 'Pull'
			WHEN 2 THEN 'Anon'
		END
,	[subscription_sync_type] = 
		CASE	sub.sync_type
			WHEN 1 THEN 'Auto'
			WHEN 2 THEN 'None'
		END
FROM
	dbo.MSarticles art
INNER JOIN	
	dbo.MSsubscriptions sub
ON	sub.publication_id = art.publication_id
AND	sub.article_id = art.article_id
INNER JOIN
	dbo.MSpublications pub
ON	sub.publication_id = pub.publication_id
WHERE	
	sub.subscriber_id > 0
ORDER BY
	sub.[status]
,	pub.publisher_db 
,	sub.subscriber_id
,	sub.subscriber_db
,	pub.publication;