Listing encrypted columns–AE learning series part 03

  • Article

In the first two parts (part 1, part 2) we learned how to build our first application using Always Encrypted and import new or existing data in it.

In the following post I want to give you some details on the available metadata for Always Encrypted. There are some changes in existing DMVs (such as sys.columns) and some brand new views, that will help SQL Server professionals get information on the AE metadata.

Finding encrypted columns in a SQL Server

To specifically track if you have encrypted columns in your database, you can use the below script. It utilizes sys.columns system view that introduces several new columns in SQL Server 2016.

 SELECT c.name,
 c.column_encryption_key_id,
 CASE c.column_encryption_key_database_name
 WHEN NULL
 THEN DB_NAME()
 ELSE c.column_encryption_key_database_name
 END AS columns_encryption_key_database_name,
 c.encryption_type_desc,
 c.encryption_algorithm_name
 FROM sys.columns c
 WHERE c.encryption_type_desc IS NOT NULL;