--AG Status --lIKE Listener/Listerner_Status/AG_Name/AG_Role/Seeding_mode/Failove_Mode/Health_status/ListnerIP/Availability_Mode/Read_Only Destination/

 

select ag.name as "Availability Group"

 , ar.replica_server_name as "Source Replica"

 , ar2.replica_server_name as "Read-Only Destination"

 , ars.role_desc as "AG_Role"

 , al.dns_name "Listener"

 , ar.failover_mode_desc "Failover_Mode"

 , ar.seeding_mode_desc

 , rl.routing_priority as "Routing Priority"

 , ar.secondary_role_allow_connections_desc as "Allowed Secondary Role"

 , ar2.read_only_routing_url as "Read-Only Routing Url"

 , ah.synchronization_health_desc "HealthStatus"

 , agl.ip_address "ListenerIPaddress"

 , state_desc "Listener_Status"

 , ar.availability_mode_desc "Availability_Mode"

from sys.availability_read_only_routing_lists rl

 inner join sys.availability_replicas ar on rl.replica_id = ar.replica_id

 inner join sys.availability_replicas ar2 on rl.read_only_replica_id = ar2.replica_id

 inner join sys.dm_hadr_availability_replica_states ars on ars.replica_id=ar.replica_id

 inner join sys.availability_groups ag on ar.group_id = ag.group_id

 inner join sys.dm_hadr_availability_group_states ah on ah.group_id = ag.group_id

 inner join sys.availability_group_listeners al on al.group_id = ag.group_id

 inner join sys.availability_group_listener_ip_addresses agl on agl.listener_id=al.listener_id

order by ag.name, ar.replica_server_name, rl.routing_priority