Helpful TSQL Queries for meta search

Searching for table names 

SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME LIKE '%prop%'

Searching for Stored Procedure and more containing text 

SELECT OBJECT_NAME(object_id)
FROM sys.sql_modules
WHERE OBJECTPROPERTY(object_id, 'IsProcedure') = 1
    AND definition LIKE '%CounterpartyProperties%'
ORDER BY OBJECT_NAME(object_id)

OBJECTPROPERTY PropertyName reference list:

https://msdn.microsoft.com/en-us/library/ms176105.aspx