Get TDE (Transparent Data Encryption) Info - along with encryption state, certificate, etc

Get_TDE_Information.sql

/*************************************************************************************************************************************************
Author		:		██╗  ██╗██╗███╗   ██╗    ███████╗██╗  ██╗ █████╗ ██╗  ██╗
				██║ ██╔╝██║████╗  ██║    ██╔════╝██║  ██║██╔══██╗██║  ██║
				█████╔╝ ██║██╔██╗ ██║    ███████╗███████║███████║███████║
				██╔═██╗ ██║██║╚██╗██║    ╚════██║██╔══██║██╔══██║██╔══██║
				██║  ██╗██║██║ ╚████║    ███████║██║  ██║██║  ██║██║  ██║
				╚═╝  ╚═╝╚═╝╚═╝  ╚═══╝    ╚══════╝╚═╝  ╚═╝╚═╝  ╚═╝╚═╝  ╚═╝ (@TheRockStarDBA)

				Sr. DBA 
Date		:	- Original Version created on 01.15.2016
				

Comments	:	- This script list out the databases, encryption status along with other useful information.
				- Use this script if you have TDE enabled in your environment.

Usage		:	- You can use this script free by keeping this header as is and give due credit to the author of this script which is ME :-)
				- Use it as per your risk --> Neither Me or my employer is responsible for any 
						- DATA LOSS !!
						- Financial LOSS !!
						- Emotional LOSS !!
						- ANY LOSS THAT YOU CAN THINK !!
**************************************************************************************************************************************************/


set nocount on
if exists (select 1 from master.sys.databases where is_encrypted = 1)
begin
select DB_NAME(db.database_id) as DatabaseName
	,c.name as CertificateName
	,case 
		when dek.encryption_state = 0
			then 'No database encryption key present, no encryption'
		when dek.encryption_state = 1
			then 'Unencrypted'
		when dek.encryption_state = 2
			then 'Encryption in progress'
		when dek.encryption_state = 3
			then 'Encrypted'
		when dek.encryption_state = 4
			then 'Key change in progress'
		when dek.encryption_state = 5
			then 'Decryption in progress'
		when dek.encryption_state = 6
			then 'Protection change in progress (The certificate or asymmetric key that is encrypting the database encryption key is being changed.)'
		end as encryption_state_desc
	,dek.create_date
	,regenerate_date
	,modify_date
	,set_date
	,opened_date
	,key_algorithm
	,key_length
	,encryptor_thumbprint
	,case 
		when dek.percent_complete = 0
			then 'no state change'
		else cast(dek.percent_complete as varchar(5))
		end as percent_complete
	,[certificate_id]
	,[principal_id]
	,[pvt_key_encryption_type]
	,[pvt_key_encryption_type_desc]
	,[issuer_name]
	,[cert_serial_number]
	,[subject]
	,[expiry_date]
	,[start_date]
	,[thumbprint]
	,[pvt_key_last_backup_date]
from master.sys.dm_database_encryption_keys as dek
left join master.sys.certificates as c on dek.encryptor_thumbprint = c.thumbprint
inner join master.sys.databases as db on dek.database_id = db.database_id
order by DB_NAME(db.database_id)
end
else 
select 'There is no TDE encryption configured on '+@@servername + ' !!' as Information