SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Get your favorite SSC scripts directly in SSMS with the free SQL Scripts addin. Search for scripts directly from SSMS, and instantly access any saved scripts in your SSC briefcase from the favorites tab.
Download now (direct download link)

Find a string in database objects

By Laurent Michaud,

My first script will be very simple but extremely useful for every DBA (well, at least it is useful for me).

A few months ago, I was still using a very helpful program called "SQL Server Find", which allowed me to find a string inside all database object definitions (stored procedures, tables, functions, etc.). But this program was really slow to output results and I had to edit every object to see if the string found was really what I was looking for.

Then I discovered OBJECT_DEFINITION and it changed my life :)

Stored procedure to find a string in database objects

Compared to articles in reference, this SP gives more information about the found objects : type, creation date, modification date and a substring of @intNbCharToExtract characters before and after the string to find.

You can also limit the results to a particular object type (see this MSDN article for more info).


By executing the following code :

exec DBA_FindStringInDB @chvStringToFind='sp_helpdia', @chrObjectType=null, @intNbCharToExtract=50

I get this results :

References :

Pinal Dave's blog article

Another article on SQL Server Central about this subject

Total article views: 2953 | Views in the last 30 days: 2
Related Articles

How to save results of a procedure with more than one result set

Sometimes a procedure returns more than 1 result set. The article describes how to save all result s...


Simple Object and String Search within Objects

This Simple script will help to do a Server wide search for an object name or usage of a String in O...


Object Search

Procedure to search any database (or a combination of databases, including all) for a specific strin...


String Operation in Stored Procedure

String Operation in Stored Procedure


Create procedure and filter the result

Create procedure and filter the result

string match