Searching through SPs, Tables, Views

This little procedure is very useful during development / support to quickly find all objects that may have something to do with a certain term, column name, part of thereof and so on.

CREATE PROCEDURE adhoc_SearchText(@text VARCHAR(1024))
AS
BEGIN
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES T
WHERE charindex(@text, T.TABLE_NAME)>0

SELECT C.TABLE_NAME, C.COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS C
WHERE charindex(@text, C.COLUMN_NAME)>0

SELECT V.TABLE_NAME AS VIEW_NAME
FROM information_schema.VIEWS V
WHERE charindex(@text, V.VIEW_DEFINITION)>0

SELECT R.ROUTINE_NAME
FROM information_schema.routines r
WHERE charindex(@text, r.ROUTINE_DEFINITION)>0
END