To list all databases in a SQL Server instance with the information if the database is configured for mirroring use the following query:
SELECT d.name AS Database_Name
,CASE
WHEN dm.mirroring_state is NULL THEN 'Not Mirrored'
ELSE 'Mirrored'
END AS Mirroring_Status
FROM sys.databases d JOIN sys.database_mirroring dm
ON d.database_id=dm.database_id
ORDER BY d.name
Here is a similar query with an added information about the mirroring state, that can for the mirrored databases be one of the following:
DISCONNECTED, SYNCHRONIZED, SYNCHRONIZING, PENDING_FAILOVER, SUSPENDED, UNSYNCHRONIZED.
SELECT d.name AS Database_Name
,CASE
WHEN dm.mirroring_state is NULL THEN 'Not Mirrored'
ELSE 'Mirrored'
END AS Mirroring_Status
, dm.mirroring_state_desc as Mirroring_State
FROM sys.databases d JOIN sys.database_mirroring dm
ON d.database_id=dm.database_id
ORDER BY d.name
To list all mirrored databases in a SQL Server instance:
SELECT d.name AS Database_Name
,CASE
WHEN dm.mirroring_state is NULL THEN 'Not Mirrored'
ELSE 'Mirrored'
END AS Mirroring_Status
FROM sys.databases d JOIN sys.database_mirroring dm
ON d.database_id=dm.database_id
WHERE dm.mirroring_state IS NOT NULL
ORDER BY d.name
