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/