T-SQL query to Get Details of AlwaysOn HighAvailability Groups AvailabilityDatabases

Here is the T-SQL query to Get Details of AlwaysOn HighAvailability Groups AvailabilityDatabases.

select * into #myTempag_availability_groups from master.sys.availability_groups
select replica_id, group_id into #myTempardb_availability_replicas from master.sys.availability_replicas
select replica_id, group_database_id, database_name,is_database_joined,is_failover_ready,is_pending_secondary_suspend,recovery_lsn,truncation_lsn into #myTempardb_database_replica_cluster_states from master.sys.dm_hadr_database_replica_cluster_states
select replica_id, is_local into #myTempardb_availability_replica_states from master.sys.dm_hadr_availability_replica_states
select replica_id, group_database_id,synchronization_state, is_suspended into #myTempardb_database_replica_states from master.sys.dm_hadr_database_replica_states
SELECT
dbcs.database_name AS [DatabaseName],
'Server[@Name=' + quotename(CAST(
        serverproperty(N'Servername')
       AS sysname),'''') + ']' + '/AvailabilityGroup[@Name=' + quotename(AG.name,'''') + ']' + '/AvailabilityDatabase[@Name=' + quotename(dbcs.database_name,'''') + ']' AS [Urn],
CASE
WHEN ISNULL(dbrs.synchronization_state, 0) = 0 THEN 'NOT SYNCHRONIZING'
WHEN ISNULL(dbrs.synchronization_state, 0) = 1 THEN 'SYNCHRONIZING'
WHEN ISNULL(dbrs.synchronization_state, 0) = 2 THEN 'SYNCHRONIZED'
WHEN ISNULL(dbrs.synchronization_state, 0) = 3 THEN 'REVERTING'
WHEN ISNULL(dbrs.synchronization_state, 0) = 4 THEN 'INITIALIZING'
END AS [SynchronizationState],
CASE
WHEN ISNULL(dbrs.is_suspended, 0) = 0 THEN 'RESUMED'
WHEN ISNULL(dbrs.is_suspended, 0) = 1 THEN 'SUSPENDED'
END AS [IsSuspended],
CASE 
WHEN ISNULL(dbcs.is_database_joined, 0) =  0 THEN 'NOT JOINED TO AG ON AR'
WHEN ISNULL(dbcs.is_database_joined, 0) =  1 THEN 'JOINED TO AG ON AR'
END AS [IsJoined]
FROM
#myTempag_availability_groups AS AG
INNER JOIN #myTempardb_availability_replicas AS AR ON AR.group_id=AG.group_id
INNER JOIN #myTempardb_availability_replica_states AS arstates ON AR.replica_id = arstates.replica_id AND arstates.is_local = 1
INNER JOIN #myTempardb_database_replica_cluster_states AS dbcs ON arstates.replica_id = dbcs.replica_id
LEFT OUTER JOIN #myTempardb_database_replica_states AS dbrs ON dbcs.replica_id = dbrs.replica_id AND dbcs.group_database_id = dbrs.group_database_id
WHERE
(AG.name='YourAvailabilityGroupName')
ORDER BY
[Name] ASC
DROP TABLE #myTempardb_availability_replicas
DROP TABLE #myTempardb_database_replica_cluster_states
DROP TABLE #myTempardb_availability_replica_states
DROP TABLE #myTempardb_database_replica_states
drop table #myTempag_availability_groups