Searching in SQL Management Studio 2005

Is there a way to search for text within stored procedures? For example I want to find out if a particular table is being referenced by any stored procedure.

From stackoverflow Prabhu

• Use:

  SELECT OBJECT_NAME(m.object_id), m.*
    FROM SYS.SQL_MODULES m
   WHERE m.definition like N'%text_youre_looking_for%'
  

  SYSCOMMENTS and INFORMATION_SCHEMA.routines have NVARCHAR(4000) columns. So if "text_youre_looking_for" is used at position 3998, it won't be found. SYSCOMMENTS does have multiple lines, but INFORMATION_SCHEMA.routines truncates.

  JNK : Thanks for setting me straight.

  OMG Ponies : @JNK: For the record, I didn't downvote you.

  JNK : @OMG - no biggie, I posted a wrong answer :)

  gbn : @JNK: I did but decided to do my answer first beore commenting

  JNK : @gbn: As I said, no biggie it was a wrong answer. that also explains an issue I had with that script in the past.

  Mike Cheel : Don't forget if you just care about references and you have the permissions you can just right click the sproc in Object Explorere and select View Dependencies. And this is in addition to what the others have said so please don't downvote me like some did earlier today on a different post when I was just giving additional information.

  gbn : @Mike Cheel: it's not reliable. Try dropping/recreating a base table used by a view. Dependencies are *not* maintained (though it's getting better)
  From OMG Ponies

• SELECT
     OBJECT_SCHEMA_NAME(object_id) + '.' + OBJECT_NAME(object_id)
  FROM
     sys.sql_modules
  WHERE
     definition like '%whatever%'
  

  syscomments is legacy and splits the definition into nvarchar 4000 chunks thus risking not finding what you want. The same applies to INFORMATION_SCHEMA.ROUTINES

  KM : +1, `OBJECT_SCHEMA_NAME()` is way better than joining to sys.objects and then sys.schemas!!
  From gbn

• You can script it out and search the script.

  From Beth

• My co-worker graciously provided me with this one recently. It does some similar searching as others have noted, but with a little more added in.

  DECLARE
   @chvStringToFind varchar(256), /* String to find */
   @chrObjectType char(2),--=null, /* Only look for objects of this type */
   @intNbCharToExtract int
   --=50 /* Number of characters to extract before and after the string to find */
   --exec DBA_FindStringInDB @chvStringToFind='sp_helpdia', @chrObjectType=null, @intNbCharToExtract=50
   SET @chvStringToFind = 'EnterSearchTextHere'  -- Change this to search
   SET @chrObjectType = NULL
   SET @intNbCharToExtract = 50
  
   SELECT t.Name, t.TypeDescription, t.CreationDate, t.ModificationDate,
   '...' + SUBSTRING
   (
   t.ObjectDefinition,
   CHARINDEX(@chvStringToFind, t.ObjectDefinition) - @intNbCharToExtract,
   LEN(@chvStringToFind) + (@intNbCharToExtract*2)
   ) + '...' AS Extract
   FROM
   (
   SELECT o.name AS Name, 
   o.type_desc AS TypeDescription, 
   o.create_date AS CreationDate, o.modify_date AS ModificationDate,
   OBJECT_DEFINITION(object_id) AS ObjectDefinition
   FROM sys.objects o
   WHERE 
   ((o.type IN ('AF', 'FN', 'IF', 'P', 'TF', 'TT', 'U', 'V', 'X') AND @chrObjectType IS NULL) OR o.type = @chrObjectType)
   AND OBJECT_DEFINITION(o.object_id) LIKE '%' + @chvStringToFind + '%'
   ) AS t
   ORDER BY TypeDescription, Name
  

  From Vinnie

• Red Gate has a free tool called Sql Search that I rather like. It keeps an index so that after its first search, it is very fast (and even the first one is pretty good...). It can search for text in procs as well as table and view definitions, etc. There are some filtering features to make it a little easier to use. And the search results are displayed in a very useful manner, with the full text of the object and the search text highlighted.

  Prabhu : Great, I knew they had one, but wasn't sure I didn't know it was free.

  marc_s : +1 excellent tool from a great company - highly recommended ! Couldn't live without it anymore.....
  From Ray