How to List all Mirrored SQL Server Databases

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