- 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
