The easiest way seems to be using the sys.crypt_properties Catalog View which holds the relationship between Certificates / Asymmetric Keys and signed modules, as well as the usage (i.e. Signature or Counter-Signature). There are a few other means of finding which modules are signed, but none seem to indicate Signature vs Counter-Signature.
SELECT SCHEMA_NAME(so.[schema_id]) AS [SchemaName],
so.[name] AS [ObjectName],
so.[type_desc] AS [ObjectType],
---
scp.crypt_type_desc AS [SignatureType],
ISNULL(sc.[name], sak.[name]) AS [CertOrAsymKeyName],
---
scp.thumbprint
FROM sys.crypt_properties scp
INNER JOIN sys.objects so
ON so.[object_id] = scp.[major_id]
LEFT JOIN sys.certificates sc
ON sc.thumbprint = scp.thumbprint
LEFT JOIN sys.asymmetric_keys sak
ON sak.thumbprint = scp.thumbprint
WHERE so.[type] <> 'U'
ORDER BY [SchemaName], [ObjectType], [ObjectName], [CertOrAsymKeyName];
In my test DB, this query returns the following:
Schema ObjectName ObjectType SignatureType CertOrAsymKeyName thumbprint
------ ---------- ---------- ------------- ----------------- ----------
dbo fnPaymentCalc SQL_SCALAR_FUNCTION COUNTER SIGNATURE BY ASYMMETRIC KEY KeyTest1 0x2333B2FA6AA8004E
dbo ModuleTest2 SQL_STORED_PROCEDURE SIGNATURE BY CERTIFICATE CrossDatabaseCert 0x49BA174584C78C878D923690C15898A809CBACDF
dbo TestSig SQL_STORED_PROCEDURE SIGNATURE BY CERTIFICATE bob 0x778B3DB4ED981FC27AB301ACE7A1AB8424F64792
dbo TestSig SQL_STORED_PROCEDURE SIGNATURE BY ASYMMETRIC KEY KeyTest1 0x2333B2FA6AA8004E