Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
In particular, if you are using Always On Availability Group and you haven’t included some of your databases to an always on availability groups, in some cases you may need list of databases that are not in the availability group.
You can learn more about Always On Availability Group from the article named “SQL Server Always On Availability Group”
The following 2 scripts provide a list of standalone databases on the instance. I wrote the second one when I couldn’t find the first one. You can use whichever you want.
Script 1
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | Select name from sys.sysdatabases where name not in( Select DISTINCT drcs.database_name AS name FROM master.sys.availability_groups AS ag LEFT OUTER JOIN master.sys.dm_hadr_availability_group_states as ags ON ag.group_id = ags.group_id INNER JOIN master.sys.availability_replicas AS ar ON ag.group_id = ar.group_id INNER JOIN master.sys.dm_hadr_availability_replica_states AS ars ON ar.replica_id = ars.replica_id AND ars.is_local = 1 INNER JOIN master.sys.dm_hadr_database_replica_cluster_states AS drcs ON ars.replica_id = drcs.replica_id LEFT OUTER JOIN master.sys.dm_hadr_database_replica_states AS dbrs ON drcs.replica_id = dbrs.replica_id AND drcs.group_database_id = dbrs.group_database_id WHERE ISNULL(ars.role, 3) = 1 OR ISNULL(drcs.is_database_joined, 0) = 1 )and dbid >4 order by name asc |
Script 2
1 2 3 4 5 6 7 | SELECT name FROM sys.sysdatabases where dbid>4 and name not in( SELECT DB_NAME(drs.database_id) as name FROM (( sys.availability_groups AS ag JOIN sys.availability_replicas AS ar ON ag.group_id = ar.group_id ) JOIN sys.dm_hadr_availability_replica_states AS ars ON ar.replica_id = ars.replica_id) JOIN sys.dm_hadr_database_replica_states AS drs on ag.group_id = drs.group_id and drs.replica_id = ars.replica_id where ars.role_desc='PRIMARY' OR ars.role_desc='SECONDARY') |
