Recently I helped with a data center cut over.  Moving databases and making sure all the procedures and views inside them still worked was a high priority.  But how do you find all the Procedures and Views in a database that reference things outside the database?  I needed some kind of keyword search, like bingle for my database.  I found one that used the Information_Schema.Routines and improved on it so that it doesn’t cut off at 4000 characters. Here is the procedure that needs to be created:

CREATE PROC FindProcContaining
    @search VARCHAR(100) = ''
AS
SET @search = '%' + @search + '%'
SELECT name, definition, type_desc
FROM sys.sql_modules m
INNER JOIN sys.objects o ON m.object_id = o.object_id
WHERE definition LIKE @search
ORDER BY definition 

This is what your execute will look like:

EXEC FindProcContaining 'SearchCriteria'

It will return a list of the View or Procedure Name, the Syntax if it is not encrypted and the type of either View or Stored Procedure. I was super excited about it.