SELECT
    ag.name AS AvailabilityGroupName,
    ar.replica_server_name AS ReplicaServerName,
    adc.database_name AS DatabaseName,
    drs.synchronization_state_desc AS SynchronizationState,
    drs.database_state_desc AS DatabaseState,
    drs.synchronization_health_desc AS SynchronizationHealth
FROM
    sys.dm_hadr_availability_group_states ags
    INNER JOIN sys.availability_groups ag ON ag.group_id = ags.group_id
    INNER JOIN sys.dm_hadr_availability_replica_states ars ON ag.group_id = ars.group_id
    INNER JOIN sys.availability_replicas ar ON ars.replica_id = ar.replica_id
    INNER JOIN sys.dm_hadr_database_replica_states drs ON ars.replica_id = drs.replica_id
    INNER JOIN sys.availability_databases_cluster adc ON drs.group_database_id = adc.group_database_id
ORDER BY
    ag.name, ar.replica_server_name, adc.database_name;