How to get list of all always encrypted columns in SQL Server

We do have “Always encrypted” feature in SQL 2016 and later versions, from this feature we can encrypt the column data instead of encrypting whole database.  Sensitive data like credit card numbers, SSN.
We need to prepare a list of encrypted columns at some where to track for future.
Without tracking anywhere in the documents we can run the simple SQL query which returns all the columns in respective tables and encryption type.

[sql]
SELECT
t.name AS TableName,
c.name AS ColumnName,
k.name AS KeyName,
c.encryption_type_desc,
c.encryption_algorithm_name
FROM sys.columns c
INNER JOIN sys.column_encryption_keys k ON c.column_encryption_key_id = k.column_encryption_key_id
INNER JOIN sys.tables t ON c.object_id = t.object_id
WHERE encryption_type IS NOT NULL
[/sql]

 

 


Posted

in

, , ,

by

Comments

7 responses to “How to get list of all always encrypted columns in SQL Server”

  1. Fitness Tips Avatar

    Thank you for being of assistance to me. I really loved this article.

  2. Beauty Fashion Avatar

    Thank you for writing this post. I like the subject too.

  3. twicsy reviews Avatar

    Excellent article! We will be linking to this great article on our website.
    Keep up the great writing.

  4. instant instagram followers buy Avatar

    I got this site from my friend who shared with me about this web page and at the moment this time
    I am visiting this web page and reading very informative articles
    or reviews here.

  5. buy real instagram followers (2021) Avatar

    We’re a group of volunteers and starting a new scheme in our community.

    Your website offered us with valuable info to work on. You have done an impressive
    activity and our whole community shall be thankful to you.

  6. buy instagram followers with instant delivery Avatar

    Fine way of describing, and good article to get data about
    my presentation subject, which i am going to
    convey in academy.

  7. Hairstyles Avatar

    Whats up very cool site!! Guy .. Excellent .. Superb .. I’ll bookmark your blog and take the feeds additionally厈I am glad to search out a lot of helpful info here within the submit, we’d like develop more strategies in this regard, thanks for sharing. . . . . .

Leave a Reply

Your email address will not be published. Required fields are marked *