How to find usage of sql within the database

Have you ever was in that situation as I do from time to time ?

• Remember the column or table in the database, but don't remember, where its exactly using and how ?
• Trying to  remember all stored procedures, functions or triggers that uses  some text, not matter its sql word or some name?
• Aasking yourself something like "A month ago, i wrote the procedure that has 'hallo world' text in it. What's the name of it?"

I'm falling in this situation quite often so decided to wrote something to make my life easier.
What I have now is small script that's actually kind of full-text search within database's programmability objects (procedures, function, triggers and so on).

Here is it:

declare @textToSearch varchar(max)
set @textToSearch = '%EmailLog%'

declare @total int;

with t (
    rownumber,
    name)
as
(
    select
        ROW_NUMBER() OVER (ORDER BY so.name) as rownumber,
        so.name 
    from
        syscomments sc 
        inner join sysobjects so on so.id = sc.id 
    where text like @textToSearch
)
select @total =count(*) from t

declare @currentPos int
set @currentPos = 1

declare @name varchar(max)

while (@currentPos <= @total) 
BEGIN 
    with t ( rownumber, name) as ( 
        select 
            ROW_NUMBER() OVER (ORDER BY so.name) as rownumber, 
            so.name 
        from
            syscomments sc 
            inner join sysobjects so on so.id = sc.id 
        where text like @textToSearch 
    ) 
    select @name = [name] from t where rownumber = @currentPos 
    Print '----------------------------------------------------------------------------------------------------' 
    Print @name Print '----------------------------------------------------------------------------------------------------' 
    EXEC sp_helptext @name; 
    Print '----------------------------------------------------------------------------------------------------' 
    set @currentPos = @currentPos + 1 
END 
go


Enter what are you looking for to the @textToSearch variable, between two '%' in example above.

In my example, i tried to find all places (stored procedures, functions, triggers and so on) that has text 'EmailLog'

To make result more readable, switch output view from Grid to Text. Use Ctrl+T or menu Query -> Results To -> Results To Text.

Just run it and  after a ninebt you will have text of each procedure or function that contains specified word