Thursday, 26 February 2009

Search Stored Procedures in T-SQL

So, I started a blog called "dot net notes" and my first post is about SQL. Doesn't look good but in honesty this is one of the most useful things I ever learned in SQL - how to do a blanket search of the text in all the stored procedures in a given database.

SELECT Distinct SO.Name
FROM sysobjects SO (NOLOCK)
INNER JOIN syscomments SC (NOLOCK) on SO.Id = SC.ID
AND SO.Type = 'P'
AND SC.Text LIKE '%text_to_search_for%'

Just replace "text_to_search_for" with, well, whatever you want to search for and you're good to go. Immensely helpful for tracking down chains of stored procedures which call each other, or examples of something you've done before in SQL but can't quite remember, or finding all the SP's in a database which reference a particular table or field. With enough tweaking it'd probably make you tea in the morning and wipe your arse for you.

No comments:

Post a Comment