THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

John Paul Cook

SQL Search - free tool for searching your SQL Server

I downloaded Red Gate's new free utility SQL Search earlier this week. A coworker just asked me if there is an easy way to search all stored procedures for a specific string. SQL Search was the answer! It exceeded my expectations. SQL Search integrates with SSMS and is completely intuitive.
Published Thursday, February 11, 2010 11:52 AM by John Paul Cook

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS



AaronBertrand said:

John, the "many rows" problem is only true if you are still using SQL Server 2000.  In SQL Server 2005 and up, the sys.sql_modules.definition column is actually NVARCHAR(MAX), and you will only ever find one row per object, so the string splitting part isn't an issue.  You can also use the OBJECT_DEFINITION() function, which returns a single NVARCHAR(MAX) value as well.

I wrote about how I do this in my environment here:

That said, I do agree that for a free tool, this one is definitely worth a look.  It does have several features that would be difficult to reproduce with native tools and without a lot of hard work.

February 11, 2010 12:28 PM

John Paul Cook said:

Quite correct as always, Aaron. I removed that tangent from the post. Thanks for the correction.

February 11, 2010 1:24 PM

jerryhung said:

Too bad SQL Search doesn't work on SQL 2000, only on 2005/2008

but it's such a nice add-on to have for SSMS

February 11, 2010 2:29 PM

Dan said:

jerryhung, you could always search syscomments for stored procedure text, the id column is the object_id.

select * from syscomments where text like '%SPText%'

February 11, 2010 3:17 PM

Harsh said:


Does it have some intelligence built-in like to skip the text which is part of comments?


Even better, you can use information_schema views but that would be crude search

February 11, 2010 4:52 PM

John Paul Cook said:

It doesn't ignore text that is commented out. You should try it out. I'll think you'll find it handy. I wrote my own search tool once and this one is way better than mine!

February 11, 2010 5:17 PM

Leave a Comment


About John Paul Cook

John Paul Cook is a database and Azure specialist in Houston. He previously worked as a Data Platform Solution Architect in Microsoft's Houston office. Prior to joining Microsoft, he was a SQL Server MVP. He is experienced in SQL Server and Oracle database application design, development, and implementation. He has spoken at many conferences including Microsoft TechEd and the SQL PASS Summit. He has worked in oil and gas, financial, manufacturing, and healthcare industries. John is also a Registered Nurse currently studying to be a psychiatric nurse practitioner. Contributing author to SQL Server MVP Deep Dives and SQL Server MVP Deep Dives Volume 2. Connect on LinkedIn

This Blog



Privacy Statement