How to get the Always On Availability Group DNS name per database

Question: How can I get a list of databases on an Always On Availability Groups setup , along with the DNS name ? I need the  DNS name of the listener hostname 

Answer: This query will list a group of databases associated with availability groups in a Clustered Always On setup .  The sys.availability_group_listeners view as defined on Microsoft docs "For each Always On availability group, returns either zero rows indicating that no network name is associated with the availability group, or returns a row for each availability-group listener configuration in the Windows Server Failover Clustering (WSFC) cluster."

This query uses a left join to the sys.availability_group_listeners  view. If you execute the same query on a Clusterless Always On set up , you will return a list of databases within the Availability Groups but with NULL as the  DNS name. If you execute in a Clustered i.e with listeners , than the DNS_NAME column will be populated with the hostname of the availability group listeners. 

select distinct DB.name, GL.dns_name,AG.name 
from
sys.dm_hadr_availability_replica_cluster_nodes CN
join sys.availability_groups AG on AG.name = CN.group_name 
join sys.availability_replicas AR on AR.group_id= AG.group_id 
join sys.databases DB on AR.replica_id= DB.replica_id 
left outer join sys.availability_group_listeners GL on GL.group_id = AG.group_id

Read more on Always On 

Quick access to SQL Server Always On Availability cheatsheet

 How to fix Database might contain bulk logged changes that have not been backed up for Always On