Find Out All Encrypted Columns in a Database with Their Key Name
--FIND OUT ALL ENCRYPTED COLUMNS IN A DATABASE WITH THEIR KEY NAME.
--ALWAYS ENCRYPTED
SELECT *FROM sys.columns
SELECT *FROM sys.column_encryption_keys
SELECT *FROM sys.tables
SELECT tbl.name AS TableName
,col.name AS ColumnName
,col.max_length AS Length
,cek.name AS KeyName
,col.encryption_type_desc AS EncryptionType
,col.encryption_algorithm_name AS EncryptionAlgorithm
FROM sys.columns col
INNER JOIN sys.column_encryption_keys cek ON col.column_encryption_key_id = cek.column_encryption_key_id
INNER JOIN sys.tables tbl ON col.object_id = tbl.object_id
WHERE encryption_type IS NOT NULL
GO