List availability groups and the local replica role
T-SQL query listing the availability groups of an instance with the current primary replica and the role of the local replica.
List the availability groups defined on the instance, with the name of the current primary replica and the role of the local replica in each group.
Query
SELECT
AG.name AS [Name],
ISNULL(agstates.primary_replica, '') AS [PrimaryReplicaServerName],
ISNULL(arstates.role, 3) AS [LocalReplicaRole]
FROM master.sys.availability_groups AS AG
LEFT OUTER JOIN master.sys.dm_hadr_availability_group_states as agstates
ON AG.group_id = agstates.group_id
INNER JOIN master.sys.availability_replicas AS AR
ON AG.group_id = AR.group_id
INNER JOIN master.sys.dm_hadr_availability_replica_states AS arstates
ON AR.replica_id = arstates.replica_id AND arstates.is_local = 1
ORDER BY [Name] ASC
Notes
LocalReplicaRoleis the numeric role of the local replica. The query substitutes 3 when no role is returned for it.