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.