SelectReplicationStatus.sql

-- Attribution to Ivan Donev
-- Details:
-- http://www.mssqlinsider.com/2013/09/check-databases-part-replication/

-- Requires SA
IF IS_SRVROLEMEMBER ('sysadmin') = 1  
   print 'Current user''s login is a member of the sysadmin role'  
ELSE IF IS_SRVROLEMEMBER ('sysadmin') = 0  
   THROW 50001, 'ERROR: Current user''s login is NOT a member of the sysadmin role.', 1   
ELSE IF IS_SRVROLEMEMBER ('sysadmin') IS NULL  
   THROW 50001,  'ERROR: The server role specified is not valid.', 1;   
   
SELECT 
	name as [Database name],
	CASE is_published 
		WHEN 0 THEN 'No' 
		ELSE 'Yes' 
		END AS [Is Published],
	CASE is_merge_published 
		WHEN 0 THEN 'No' 
		ELSE 'Yes' 
		END AS [Is Merge Published],
	CASE is_distributor 
		WHEN 0 THEN 'No' 
		ELSE 'Yes' 
		END AS [Is Distributor],
	CASE is_subscribed 
		WHEN 0 THEN 'No' 
		ELSE 'Yes' 
		END AS [Is Subscribed]
FROM sys.databases
WHERE database_id > 4