Can you search SQL Server 2005 Stored Procedure content?

Question

SQL Server Server 2005. I'm staring at a database that has 500+ stored procedures, and trying to glean the intricacies of how they interact with the data - particularly in regards to how they insert/modify data. I was hoping to find a search" or "find" functionality that would look at the content of the actual procedure. That way, I could do a search for all procedures that do anything at all with some_table_name. The basic find functionality of SQL Management Studio looks in opened files, and Find in Files only appears to find the content if I already have the sproc opened, and even then only in the ...\Local Settings\Temp\~vs1011.sql temporary type files.

As of right now, the only way I know of getting to the underlying procedure is to right click and select "modify" (or Script Stored Procedure As => Create or Alter). Is there a faster/easier way to search/examine all the sprocs?

Answer 1

Try using third party tools such as ApexSQL Search (free) or SSMS Toolpack (free) apart from those already mentioned here.

I’ve had a similar problem in the past when I inherited 500+ objects database. My experience is that queries are ok but what really helped was third party SSMS addins.