Query to List Encryption Certificate for Databases

What certificate is being used to encrypt each of the databases on the instances.

I can get the data using the following but how do I write the queries

USE master
GO

-- this provides the list of certificates
SELECT * FROM sys.certificates

-- this provides the list of databases (encryption_state = 3) is encrypted
SELECT * FROM sys.dm_database_encryption_keys
 WHERE encryption_state = 3;

I noticed that columns sys.certifcates.thumbprint and sys.dm_database_encryption_keys.encryptor_thumbprint contain the same data.

You can join on the certificate thumbprint:

use master;
go

select
    database_name = d.name,
    dek.encryptor_type,
    cert_name = c.name
from sys.dm_database_encryption_keys dek
left join sys.certificates c
on dek.encryptor_thumbprint = c.thumbprint
inner join sys.databases d
on dek.database_id = d.database_id;

My sample output:

database_name           encryptor_type    cert_name
=============           ==============    =========
tempdb                  ASYMMETRIC KEY    NULL
AdventureWorks2012TDE   CERTIFICATE       TdeCert

StackExchange Database Administrators Q#92080, answer score: 28