Is there a query to see which stored procedures are using a specific symmetric key?

Question

Is there a query to see which stored procedures are using a specific symmetric key? Or a built in feature of SQL to see this information. The stored procedures are using:

OPEN SYMMETRIC KEY !@#$%^ WITH DECRYPTION BY CERTIFICATE !@#%^&**^ 'string'

Answer 1

While I don't know of a solution that solves your specific problem, you can search the raw source code of your stored procedures using the sys.sql_modules view. This, however, won't work with encrypted (i.e. WITH ENCRYPTION) procedures.

SELECT s.[name]+'.'+o.[name]
FROM sys.procedures AS o  -- or sys.objects for all objects
INNER JOIN sys.schemas AS s ON o.[schema_id]=s.[schema_id]
WHERE [object_id] IN (
    SELECT [object_id]
    FROM sys.sql_modules
    WHERE [definition] LIKE '%symmetric%' -- search criteria goes here
    );

Important: The query above will return all stored procedures containing the text string "symmetric", not just procedures that use symmetric encryption or keys. Remember that it will also match for instance text that is in comment blocks och quotes.