Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
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: 2839 | Views in the last 30 days: 4
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...


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


XLSExport: A CLR procedure to export proc results to Excel

A CLR procedure utilizing the NPOI library to export the results of a passed stored procedure to an ...

string match    

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones