June 15, 2011 at 6:54 am
I need to search a string in one store procedure. I used script from
http://www.sqlservercentral.com/scripts/T-SQL+Aids/31201/
and run it as
exec proc sp_grep 'liver' but got an error said that
Msg 207, Level 16, State 3, Procedure sp_search, Line 204
Invalid column name 'O'.
Msg 207, Level 16, State 3, Procedure sp_search, Line 204
Invalid column name 'R'.
How to fix it?
June 15, 2011 at 8:10 am
I'm not familiar with that script but you can just use this select to find what you want:
select name, text
from syscomments A
JOIN sysobjects B on A.id = B.id and B.type = 'P'
where text like '%search-string%'
The probability of survival is inversely proportional to the angle of arrival.
June 15, 2011 at 8:13 am
Here is a SP that I have utilized, you may want to try it. It searches more than just SPs.
CREATE PROCEDURE [dbo].[UDP_FindWordsInaSP]
@Watchword varchar(50)
AS
SELECT distinct
'type' = case type
when 'FN' then 'Scalar function'
when 'IF' then 'Inlined table-function'
when 'P' then 'Stored procedure'
when 'TF' then 'Table function'
when 'TR' then 'Trigger'
when 'V' then 'View'
end,
o.[name],
watchword = @Watchword
FROM dbo.sysobjects o (NOLOCK)
JOIN dbo.syscomments c (NOLOCK)
ON o.id = c.id
WHERE charindex(lower(@Watchword),lower(text)) <> 0
and o.type in ('FN', 'IF', 'P', 'TF', 'TR', 'V')
and o.name NOT LIKE 'dt%' and o.name NOT LIKe 'sys%'
and o.name NOT LIKE 'UDP_FindWordsInaSP'
ORDER BY type, o.[name]
/*Run as [dbo].[UDP_FindWordsInaSP] 'string you are searching for'
June 15, 2011 at 8:28 am
Thank you for help. I ran bitbucket-25253's code. It works like magic.
June 15, 2011 at 8:30 am
June 15, 2011 at 9:00 am
adonetok (6/15/2011)
Thank you for help. I ran bitbucket-25253's code. It works like magic.
Glad I was able to assist, and I thank you for letting me know.
June 15, 2011 at 9:11 am
syscomments is provided for backwards compatibility only. Its replacement is called sys.sql_modules, and using it has the advantage that all text for any object is stored in one row. syscomments starts a new row after every 4000 (I think) characters, so if you are searching for a string that heppens to be where the break occurs, you won't find it.
John
June 15, 2011 at 10:54 am
syscomments is provided for backwards compatibility only.
That is true, but I have run it in SQL 2008 and yes it does work ...
June 15, 2011 at 11:02 am
bitbucket-25253 (6/15/2011)
syscomments is provided for backwards compatibility only.
That is true, but I have run it in SQL 2008 and yes it does work ...
It never has worked actually. If by any misfortune the word you are searching for is right in the middle of the split then you'll never find it.
The only way around it in sql 2000 was to self left join to syscomments again on colid = colid + 1 and then reconcatenate the first and last characters on each side and search in there.
It doesn't happen often, but it does happen. So if fullproof is what you need then use the new objects.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply