get the list of all encrypted columns in db

• HBK_4700

  SSCrazy Eights

  Points: 8932

  More actions

  February 24, 2012 at 9:06 am

  #268940

  Hi,

  Is there any script to find all tables encrypted columns in database

  Thanks!

• HowardW

  One Orange Chip

  Points: 29109

  More actions

  February 24, 2012 at 9:32 am

  #1451477

  I'm not an expert on the encryption in SQL Server, but from looking at the BOL article on column encryption, there's no such thing.

  It's simply a varbinary column that you apply an encryption function to when you store the results, so it isn't defined as encrypted.

  You can easily find all varbinary columns in your database with something like:

  SELECT stab.name Table_Name, sc.name Column_Name FROM sys.columns sc

  INNER JOIN sys.types st ON sc.system_type_id=st.system_type_id

  INNER JOIN sys.tables stab ON stab.object_id=sc.object_id

  WHERE st.name='varbinary'

  AND stab.is_ms_shipped=0