List Mirroring Enabled Databases

--- Run from the Principal Server


SELECT @@SERVERNAME as Current_Instance,
substring(replace(sm.mirroring_partner_name,'TCP://',''),1,charindex('.',(replace(sm.mirroring_partner_name,'TCP://','')))-1) as Mirror_Server,
sm.mirroring_partner_instance as Mirror_Instance,
sd.name as Database_Name,
sm.mirroring_role_desc,
CASE
WHEN sm.mirroring_state is NULL THEN 'Mirroring not configured'
ELSE 'Mirroring configured'
END as Mirroring_Configuration,
sm.mirroring_state_desc,
sm.mirroring_safety_level_desc,
sm.mirroring_witness_name,
sm.mirroring_witness_state_desc
FROM
sys.databases sd
INNER JOIN sys.database_mirroring sm
ON sd.database_id = sm.database_id WHERE sd.database_id > 4
AND sm.mirroring_state is not null


--- Run from the Mirror Server

SELECT @@SERVERNAME as Current_Instance,
substring(replace(sm.mirroring_partner_name,'TCP://',''),1,charindex('.',(replace(sm.mirroring_partner_name,'TCP://','')))-1) as Mirror_Server,
sm.mirroring_partner_instance as Mirror_Instance,
sd.name as Database_Name,
sm.mirroring_role_desc,
CASE
WHEN sm.mirroring_state is NULL THEN 'Mirroring not configured'
ELSE 'Mirroring configured'
END as Mirroring_Configuration,
sm.mirroring_state_desc,
sm.mirroring_safety_level_desc,
sm.mirroring_witness_name,
sm.mirroring_witness_state_desc
FROM
sys.databases sd
INNER JOIN sys.database_mirroring sm
ON sd.database_id = sm.database_id WHERE sd.database_id > 4
AND sm.mirroring_state is not null