March 22, 2006 at 9:25 am
Hello, I was asked yesterday if there was a way to look for certain words inside a sproc. I'm told them I'm not sure if there's a way to look/search for certain words inside a sproc. Currently, my coworker is looking thru thousands and thousands of sproc manually and using the Find function.
Any suggestions to help him become a bit more speedy?
Thanks.
-----------------------------
www.cbtr.net
.: SQL Backup Admin Tool[/url] :.
March 22, 2006 at 9:38 am
There is a system table, unfortunately I've forgotten the name of it, but the proc text is held in the text field in this table, so long as it's not encrypted, and can be queried against. A link back to sysobjects from this table will give you the proc name the text is related to. If I had access to a database right now, I could find the table name for you, but I do not. Surely someone can fill in the table name before I would be able to get it posted......but I've used this method to find procs which referenced certian tables and so forth before.
March 22, 2006 at 9:45 am
You could also script the stored procedures out of the db using the generate sql script functionality. This can script them to one file where you could use find. You could also script them to individual files and use grep.
Chris
March 22, 2006 at 10:44 am
Hello Oberion,
You can use
sp_helptext 'ur procedure name'
Thanks and have a great day!!!
Lucky
March 22, 2006 at 12:38 pm
Scorpion_66, if you can find the table please let me know. I will look in BOL to see if the information is in there.
Lucky, thanks for telling me about sp_helptext 'sproc_name'. We might be able to put that into another sproc and use the patindex function to find the text we need.
-----------------------------
www.cbtr.net
.: SQL Backup Admin Tool[/url] :.
March 22, 2006 at 12:49 pm
be aware that sp_helptext only gives you the first 255 characters of the proc text per line........but if you examine the proc, it gives the table name I can't think of....which is syscomments, by the way....
I modified the proc sp_helptext to have a longer 8000 character limit, and recompiled it as sp_helptext2 and use it instead......of course, if you have no procs having and single line longer than 255 chars, the origional one works fine....
March 22, 2006 at 12:50 pm
I have 2 ways I know of to search SPs
1) Script all the SPs at once into 1 huge script, then use "find"
2) query syscomments table in the DB.. Select * from syscomments where text like '%whatIwant%'
March 22, 2006 at 1:11 pm
yeah I was looking at that sp_helptext, thought about trying another SQL analyzer app, but looking at the sproc itself, its hard coded for 255 char.
Thanks for the help, everyone.
-----------------------------
www.cbtr.net
.: SQL Backup Admin Tool[/url] :.
March 22, 2006 at 1:16 pm
I've used this query:
-- search for string in stored procedures in DB
--
-- note: the reason this joins to a second instance of syscomments is to find cases where
--the string being searched for 'spans' two rows in syscomments. (the text of the stored
--procedure is whacked into 'chunks' and stored in the .text column of multiple rows in
--syscomments)
select distinct so.name from sysobjects so
join syscomments sc on sc.id = so.id
left outer join syscomments sc1 on sc1.id = sc.id and sc1.colid = sc.colid + 1
where so.type = 'p'
and ( sc.text like '%string%'
or right(sc.text,500) + left(isnull(sc1.text,''),500) like '%string%'
)
order by so.name
You can use it with sp_MSforeachdb to search all the stored procedures in all the databases in an instance.
Greg
Greg
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply