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 :
Pinal Dave's blog article
Another article on SQL Server Central about this subject