Click here to monitor SSC
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).

Example

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
http://blog.sqlauthority.com/2007/11/10/sql-server-2005-2000-search-string-in-stored-procedure/

Another article on SQL Server Central about this subject
http://www.sqlservercentral.com/scripts/Miscellaneous/30184/

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

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...

SCRIPT

Object Search

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

FORUM

String Operation in Stored Procedure

String Operation in Stored Procedure

FORUM

Create procedure and filter the result

Create procedure and filter the result

SCRIPT

Script to Find String in Database object

Script that searches every object in a database for a specific string.

Tags
search    
string match    
 
Contribute