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