Is there any Microsoft utility that will search through the source of SPs,
triggers, and functions, looking for a string? (Something beyond the
dependencies listing).
Thanks
TimoThere isn't anything built-in that I'm aware of. You can either script the
objects to files using Enterprise Manager and then search the files or run a
query like the one below to search syscomments. Note that the query method
isn't 100% accurate since a string may be split between 2 rows.
SELECT OBJECT_NAME(id)
FROM syscomments
WHERE text LIKE '%MyString%'
Hope this helps.
Dan Guzman
SQL Server MVP
"Timo" <timo@.noneofyer.biz> wrote in message
news:usSnZrvEFHA.3936@.TK2MSFTNGP10.phx.gbl...
> Is there any Microsoft utility that will search through the source of SPs,
> triggers, and functions, looking for a string? (Something beyond the
> dependencies listing).
> Thanks
> Timo
>|||Timo
Vyas has written a great script
DROP PROCEDURE sp_FindObject
GO
CREATE PROCEDURE sp_FindObject
@.SearchString varchar (255)
AS
SET nocount ON
DECLARE @.Name varchar(255)
DECLARE @.Text nvarchar(4000)
CREATE TABLE #Objs
( ObjName varchar (255))
DECLARE Obj CURSOR
FOR SELECT [NAME],[TEXT] FROM sysobjects so, syscomments sc WHERE (so.xtype
='TR' or .....) AND so.id = sc.id
OPEN Obj
FETCH Next FROM Obj INTO @.Name,@.Text
WHILE @.@.FETCH_STATUS=0
BEGIN
IF PATINDEX(@.SearchString,@.Text) <> 0
INSERT INTO #Objs VALUES (@.Name)
FETCH Next FROM Obj INTO @.Name,@.Text
END
CLOSE Obj
DEALLOCATE Obj
SELECT objname FROM #Objs GROUP BY objname
DROP TABLE #Objs
go
EXEC sp_FindObject '%HOST_ID()%'
"Timo" <timo@.noneofyer.biz> wrote in message
news:usSnZrvEFHA.3936@.TK2MSFTNGP10.phx.gbl...
> Is there any Microsoft utility that will search through the source of SPs,
> triggers, and functions, looking for a string? (Something beyond the
> dependencies listing).
> Thanks
> Timo
>|||Dan, Uri
Thank you both for the help!
Timo
