Securing your data is very important and database encryption is just part of that landscape. Taking advantage SQL Server database encryption can be a daunting task but once it is configured it is also important to monitor it.
For those databases in your environment that require Transparent Data Encryption (TDE) this script will be invaluable for monitoring the encryption states of your databases.
SELECT
[db].name,
[db].is_encrypted,
[dek].encryption_state,
CASE [dek].encryption_state
WHEN 0 THEN 'Not Encrypted'
WHEN 1 THEN 'Unencrypted'
WHEN 2 THEN 'Encryption in progress'
WHEN 3 THEN 'Encrypted'
WHEN 4 THEN 'Key change in progress'
WHEN 5 THEN 'Decryption in progress'
WHEN 6 THEN 'Protection change in progress '
ELSE 'Not Encrypted'
END AS 'Desc'
FROM
sys.dm_database_encryption_keys [dek]
RIGHT JOIN
sys.databases [db] ON [dek].database_id = [db].database_id