Listing encrypted columns–AE learning series part 03
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;