SQL Server Performance Forum – Threads Archive

Need to find words in a stored proc

I thought there was a SQL Server stored procedure with a paramenter for the text you were looking for ‘master’.
It will then list all stored procedures that have the word ‘master’. I had it at my last company soI thought it came with SQL Server but I can’t seem to locate it. I’ve seen on the sites some already created ones. Any one have a good one?
Thanks
Susan

I’m not aware of any inbuilt proc, but the query I is select distinct object_name( id ) from syscomments where text like ‘%master%’ cheers
Twan

… where you have to add a self-join on subsequent rows for the same object id. The column called Text is actually an NVARCHAR(4000) column. If the sproc definition is longer than 4000 characters, there are multiple rows, and the break-off doesn’t consider words – it just chops off after exactly 4000 positions. So the word you’re looking for might start at position 3999, and end at position 4008.

quote:Originally posted by Twan I’m not aware of any inbuilt proc, but the query I is select distinct object_name( id ) from syscomments where text like ‘%master%’ cheers
Twan

This is SQL Server 2005. So I would guess you need to query sys.comments. —
Frank Kalis
Moderator
Microsoft SQL Server MVP
Webmaster:http://www.insidesql.de

Hi ya, syscomments is still valid in SQL2005… but yes a selfjoin would be required to get those words which happen to be broken over two lines, thanks for reminding me Adriaan Cheers
Twan

You could use DEFINTION from sys.sql_modules in 2005.

<blockquote id="quote">quote:<hr height="1" noshade id="quote">Originally posted by Twan Hi ya, syscomments is still valid in SQL2005… but yes a selfjoin would be required to get those words which happen to be broken over two lines, thanks for reminding me Adriaan Cheers Twan <hr height="1" noshade id="quote"></blockquote id="quote"> Should get a play with SQL Server 2005 one of these days. Thanks for correcting. [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] – Frank Kalis Moderator Microsoft SQL Server MVP Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>

Hi,
you may refer the article section (Aashish Kaushal’s article if i am not mistaken) refers the same . Hemantgiri S. Goswami
[email protected]
"Humans don’t have Caliber to PASS TIME , Time it self Pass or Fail Humans" – by Hemantgiri S. Goswami
————————
http://hemantgirisgoswami.blogspot.com

there is free tool call SQLDigger for this purpose. But I not sure whether it supports SQL Server 2005 —————————————-
Contributing Editor, Writer & Forums Moderator
http://www.SQL-Server-Performance.Com

]]>