[
SELECT DB_NAME(DATABASE_ID),
CASE
WHEN e.encryption_state = 0 THEN 'No database encryption key present, no encryption'
WHEN e.encryption_state = 1 THEN 'Unencrypted'
WHEN e.encryption_state = 2 THEN 'Encryption in progress'
WHEN e.encryption_state = 3 THEN 'Encrypted'
WHEN e.encryption_state = 4 THEN 'ey change in progress'
WHEN e.encryption_state = 4 THEN 'Decryption in progress'
WHEN e.encryption_state = 4 THEN 'Protection change in progress (The certificate or asymmetric key that is encrypting the database encryption key is being changed.)'
END [ENCRYPTED_STATE]
FROM sys.dm_database_encryption_keys e
select NAME, is_encrypted from sys.databases](http://Code)
As far as logic, the 3 sets of WHEN expressions for e.encryption_state = 4 seems illogical. the second and third instances of e.encryption_state = 4 will never be executed. Perhaps you meant them to be 5 and 6 rather than repeated instances of 4?
You're correct. The sequence for the output should be 4,5.6. That's a typo.
The issue is this. There is ONE user db on this box (CTRM). This case expression "should" be looking at all the db's on the box, not just the system db's, and showing the encryption state for each. However, the resultset only returns a report for Tempdb, and not the CTRM db that is on the box. When i run
If you get a row for every database for the following query, then you will get a row for every database for your original query as well. There is nothing in your case expression that would cause a row to be removed.
SELECT encryption_state
FROM sys.dm_database_encryption_keys;
If some of the databases are not encrypted, what you probably should do is a left join to sys.databases like this:
SELECT
d.name,
CASE WHEN e.encryption_state = 0
THEN 'No database encryption key present, no encryption'
WHEN e.encryption_state = 1 THEN 'Unencrypted'
WHEN e.encryption_state = 2 THEN 'Encryption in progress'
WHEN e.encryption_state = 3 THEN 'Encrypted'
WHEN e.encryption_state = 4 THEN 'ey change in progress'
WHEN e.encryption_state = 5 THEN 'Decryption in progress'
WHEN e.encryption_state = 6
THEN 'Protection change in progress (The certificate or asymmetric key that is encrypting the database encryption key is being changed.)'
ELSE 'No database encryption key present, no encryption'
END [ENCRYPTED_STATE]
FROM
sys.databases d
LEFT OUTER JOIN sys.dm_database_encryption_keys e ON
d.database_id = e.database_id;