SQL Search - free tool for searching your SQL Server

re: SQL Search - free tool for searching your SQL Server

AaronBertrand — Thu, 11 Feb 2010 17:28:20 GMT

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.

re: SQL Search - free tool for searching your SQL Server

John Paul Cook — Thu, 11 Feb 2010 18:24:58 GMT

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

re: SQL Search - free tool for searching your SQL Server

jerryhung — Thu, 11 Feb 2010 19:29:45 GMT

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

re: SQL Search - free tool for searching your SQL Server

Dan — Thu, 11 Feb 2010 20:17:32 GMT

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

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

re: SQL Search - free tool for searching your SQL Server

Harsh — Thu, 11 Feb 2010 21:52:51 GMT

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

re: SQL Search - free tool for searching your SQL Server

John Paul Cook — Thu, 11 Feb 2010 22:17:18 GMT

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!