Search in SQL Management Studio 2005

Question

Is there a way to search for text in stored procedures? For example, I want to find out if any particular table refers to any stored procedure.

Answer 1

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

syscomments is a legacy and breaks the definition into nvarchar 4000 chunks, thereby risking not finding what you want. The same goes for INFORMATION_SCHEMA.ROUTINES

Answer 2

Red Gate has a free Sql Search tool that I really like. It stores the 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 definitions of tables and views, etc. There are some filtering features to make it a little easier to use. And the search results are displayed in a very useful way, while all the text of the object and the search text are highlighted.

Answer 3

Using:

 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. Therefore, if "text_youre_looking_for" is used at position 3998, it will not be found. SYSCOMMENTS has multiple lines, but INFORMATION_SCHEMA.routines truncates.

Answer 4

My colleague has kindly provided me with this one recently. It performs some similar searches, as others have noted, but with a bit more added.

 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 

Answer 5

You can execute the script and search for the script.