THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - 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 http://www.red-gate.com/products/SQL_Search/index.htm 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

Comments

 

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:

http://sqlblog.com/blogs/aaron_bertrand/archive/2009/11/09/18684.aspx

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:

John,

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

@Dan,

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

(required) 
(required) 
Submit

About John Paul Cook

John Paul Cook is a Technology Solutions Professional for Microsoft's data platform and works out of Microsoft's Houston office. Prior to joining Microsoft, he was a Microsoft SQL Server MVP. He is experienced in Microsoft 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 who graduated from Vanderbilt University with a Master of Science in Nursing Informatics and is an active member of the Sigma Theta Tau nursing honor society. He volunteers as a nurse at safety net clinics. Contributing author to SQL Server MVP Deep Dives and SQL Server MVP Deep Dives Volume 2.

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement