Cause I am the opposite of amnesia you can search for procs and views containing…

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.

About andreaallred

SQL Server and helping people is my passion. If I can make someone laugh, I know I have made a difference.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s