title: Get encrypted column name with their encryption key and certificate in sql server
body: <p>I am using SQL Server 2008/2012.In my database I have a table which has encrypted column. The column is encrypted by using following query -</p>
  
  <p><strong>Create the example table</strong></p>
  
  <pre><code>CREATE TABLE [HR].[Employees](
        [EmployeeID]      [int]         NOT NULL,
        [EmployeeName]    [varchar](50) NULL,
        [SSN]             [varchar](20) NOT NULL,
        [EncryptedSSN]    [varbinary] (200) NULL,
   CONSTRAINT [PK_Employees] PRIMARY KEY CLUSTERED 
  (     [EmployeeID] ASC)
  )
  GO
  </code></pre>
  
  <p><strong>Set up the Master Key</strong></p>
  
  <pre><code>CREATE MASTER KEY ENCRYPTION BY 
  PASSWORD = 'T3stP@ssword'
  GO
  </code></pre>
  
  <p><strong>Create the Symmetric Key and CERTIFICATE</strong></p>
  
  <pre><code>CREATE CERTIFICATE TestCert
     WITH SUBJECT = 'SSN Encryption';
  GO
  
  CREATE SYMMETRIC KEY HRKey
      WITH ALGORITHM = DES
      ENCRYPTION BY CERTIFICATE TestCert;
  GO
  </code></pre>
  
  <p><strong>Encrypt Data</strong></p>
  
  <pre><code>OPEN SYMMETRIC KEY HRKey
     DECRYPTION BY CERTIFICATE TestCert;
  Now we can update the EncryptedSSN column of our Employees table.
  
  UPDATE [HR].[Employees]
     SET [EncryptedSSN] = EncryptByKey(Key_GUID('HRKey'), SSN);
  GO
  </code></pre>
  
  <p>I am <strong>decrypting this data</strong> by using the query - </p>
  
  <pre><code>OPEN SYMMETRIC KEY HRKey
     DECRYPTION BY CERTIFICATE TestCert;
  SELECT [SSN], 
      CONVERT(VARCHAR, DecryptByKey([EncryptedSSN])) 
      AS 'Decrypted SSN'
      FROM [HR].[Employees]
  GO
  </code></pre>
  
  <p>Here we give key/ certificate value hardcoded.</p>
  
  <p>I am getting the encrypted column name with their table by this query - </p>
  
  <pre><code>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
  </code></pre>
  
  <p>And the key and certificate list by this query - </p>
  
  <pre><code>SELECT name, key_length, algorithm_desc, create_date, modify_date
  FROM sys.symmetric_keys;
  SELECT name, subject, start_date, expiry_date 
  FROM sys.certificates
  </code></pre>
  
  <p>Now I want to get which key/certificate belongs to which encrypted column SO I can apply decryption on that column without giving hardcoded value. Please help me....</p>