Get availability group and listener of the current DB (dynamic SQL)

Question

I have hundreds of DB on an instance and I want to remove the DB from their AG (each DB is in a separate AG), remove the listener and then delete that AG. I have written the script below to automate this a bit:

DECLARE @name VARCHAR(50);
DECLARE @sql nvarchar(4000);
DECLARE db_cursor CURSOR FOR select name from sys.sysdatabases where name not in ('testing_db');

Open db_cursor
Fetch Next From db_cursor Into @name
WHILE @@FETCH_STATUS = 0  
BEGIN
     select @sql = 
     'ALTER AVAILABILITY GROUP [' + @name + '] REMOVE DATABASE [' + @name + '];' + char(10) +
     'ALTER AVAILABILITY GROUP [' + @name + '] REMOVE LISTENER ' + char(10) + 
     'DROP AVAILABILITY GROUP [' + @name + '];' + char(10) + 'GO' + CHAR(10) + CHAR(10) + CHAR(10)

     print @sql;

     fetch Next From db_cursor Into @name
END 

CLOSE db_cursor  
DEALLOCATE db_cursor 

This will give me something as the below for each DB in my instance:

ALTER AVAILABILITY GROUP [**DB1**] REMOVE DATABASE [DB1];
ALTER AVAILABILITY GROUP [**DB1**] REMOVE LISTENER N**<>** 
DROP AVAILABILITY GROUP [**DB1**];
GO

I cannot find a way to get the availability group and its listener for each DB in each run (The stuff between ** ** needs to be replaced by the AG name/AG listener accordingly). I have searched how to get an AG name/listener for a DB but all of the solutions require a lot of joins which will complicate this query. What's the neatest way to do this?

Answer 1

You need to run queries agains the DMV to get the AG name.

Here's some code you could use as a base:

select DB_NAME(AG_DB.database_id) "DB", AG.name  "AG Name", li.dns_name "Listener"
from sys.dm_hadr_database_replica_states AG_DB
join sys.availability_groups AG on AG.group_id = AG_DB.group_id
join sys.availability_group_listeners li on li.group_id = AG.group_id

This will only returns databases that are in AG (vs a query in sys.databases which will return the system DB as well).

P.s. If you do not need the listener (which seems to be the case) then you do not need the 3rd table and it will keep the query pretty simple.