Information schema

Search by table and/or column name.

SELECT COLUMN_NAME
--SELECT DISTINCT table_name
FROM INFORMATION_SCHEMA.COLUMNS
WHERE 1=1
--AND TABLE_NAME = 'mytablename'
AND LOWER(TABLE_NAME) LIKE '%mytablename%'
--AND TABLE_NAME = 'true_table_name'
--AND LOWER(TABLE_SCHEMA) LIKE '%someschema%'
AND LOWER(COLUMN_NAME) LIKE '%prefix_column_example%'
--ORDER BY COLUMN_NAME
ORDER BY TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME

Search stored procedures and functions by name. Column ROUTINE_DEFINITION is the definition text of the function or procedure.

SELECT *
FROM INFORMATION_SCHEMA.ROUTINES
WHERE LOWER(ROUTINE_NAME) LIKE '%searchstring%'

Dummy true expression

Use 1=1:

SELECT *
FROM INFORMATION_SCHEMA.ROUTINES
WHERE 1=1
AND LOWER(ROUTINE_NAME) LIKE '%searchstring%'
OR ...
AND ...


References