Get encrypted column name with their encryption key and certificate in sql server

After search and try I found the solution that is -

SELECT DISTINCT key_name(encryptedcol) FROM encryptedTable;

This query gives result the encrypted key which is belong to that column.

I am using SQL Server 2016 .

Below here is the query to get all required Encrypted columns with key.

SELECT t.name AS TableName    ,c.name AS ColumnName    ,c.max_length    ,k.name AS KeyName    ,c.encryption_type_desc    ,c.encryption_algorithm_nameFROM sys.columns cINNER JOIN sys.column_encryption_keys k ON c.column_encryption_key_id = k.column_encryption_key_idINNER JOIN sys.tables t ON c.object_id = t.object_idWHERE encryption_type IS NOT NULL    

You can't, at least using T-SQL built-in system functions and views.

Also, a column's type can be varbinary and the data there is not necessary to be encrypted.

If you want to build dynamic T-SQL statements and to avoid certificates and keys hard-coding, then you can create a mapping table. For example, something like this:

TableNameColumnNameCertificateEncryptionKeyIsSymmetric

It is not the perfect work-around (yes,you need to maintained the data), but if new columns are not encrypted constantly it may do the job.