Thursday, January 16, 2014

How to Find All Ecrypted Columns in a SQL Server Database

I needed to nullify the data in all encrypted columns in a development server (according to our business rules). I knew most of the columns because we used them regularly, but I wanted to be thorough and also I wanted to be able to prove that I've found them all.

I asked the question Is there a quick way to find all columns in SQL Server 2008 R2 that are encrypted/have encrypted data on Stack Exchange and got a couple of great responses that pointed to "no" but someone came through with the answer and it worked. Basically you have to use the KEY_NAME() function and loop through the system tables to get what you need. 

I won't go into great detail here since it's all in the web page linked above. 

No comments:

Post a Comment