Find a string in Procedures, Triggers, etc

,

I want to thank James Travis for his original and excellent work and to Razvan Socol for alerting about a similar problem that led me to improve James' code. The problem is that when searching for a word that is split between two 8kb blocks, James' code would ignore it. The solution is to look for the parts in the adjacent blocks. This code works for words up to 10 chars but the code can be changed to allow longer words. The original code is here: http://www.planet-source-code.com/vb/scripts/showcode.asp?lngWId=5&txtCodeId=414

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO


CREATE PROCEDURE sp_FindStringInCode2
-- By: James Travis
-- Adapted By: Joseph Gama

/* Input variables, DEFAULT NULL FOR custom error output. */
@find VARCHAR(50) = NULL,
@type VARCHAR(2) = NULL
AS
/* CHECK FOR NULL or invalid input AND show custom error. */
IF @find IS NULL AND @type IS NULL


    BEGIN
    	RAISERROR ('This PROCEDURE has two required parameters @find AND @type',16,-1)
    	RETURN
END
ELSE IF @find IS NULL


    BEGIN
    	RAISERROR ('You must enter a valid LIKE criteria FOR @find without the leading/ending % wildcard.',16,-1)
    	RETURN
END
ELSE IF @type IS NULL OR @type NOT IN ('C','D','FN','P','TR','V')


    BEGIN
    	RAISERROR('No value was entered FOR @type.
    Valid VALUES FOR @type are
    	C = CHECK CONSTRAINT
    	D = DEFAULT
    	FN = Function
    	P = PROCEDURE
    	TR = TRIGGER
    	V = View',16,-1)
    	RETURN
END
/* SET wildcards ON END OF find value. */
DECLARE @find2 VARCHAR(50), @LenFind int
SET @LenFind=datalength(@find)
SET @find2 = @find
SET  @find= '%' + @find + '%'
/* Output object names which contain find value. */
SELECT DISTINCT OBJECT_NAME([id]) FROM syscomments c
WHERE [id] IN (SELECT [id] FROM sysobjects o WHERE xtype = @type AND status >= 0) 
AND [text] LIKE @find

--2 CHAR
OR ((@LenFind=2) AND (colid>1) AND (LEFT(c.text,1)= RIGHT(@find2,1)) AND (
EXISTS(SELECT c2.text
FROM         sysobjects o2 INNER JOIN
                      syscomments c2 ON c2.id = o2.id
where c2.id =c.id  AND   c2.colid=c.colid-1  AND (RIGHT(c2.text,1)= LEFT(@find2,1)))
))

--3 CHAR
OR ((@LenFind=3) AND (colid>1) AND (LEFT(c.text,1)= RIGHT(@find2,1)) AND (
EXISTS(SELECT c2.text
FROM         sysobjects o2 INNER JOIN
                      syscomments c2 ON c2.id = o2.id
where c2.id =c.id  AND   c2.colid=c.colid-1  AND (RIGHT(c2.text,2)= LEFT(@find2,2)))
))

OR ((@LenFind=3) AND (colid>1) AND (LEFT(c.text,2)= RIGHT(@find2,2)) AND (
EXISTS(SELECT c2.text
FROM         sysobjects o2 INNER JOIN
                      syscomments c2 ON c2.id = o2.id
where c2.id =c.id  AND   c2.colid=c.colid-1  AND (RIGHT(c2.text,1)= LEFT(@find2,1)))
))

--4 CHAR
OR ((@LenFind=4) AND (colid>1) AND (LEFT(c.text,1)= RIGHT(@find2,1)) AND (
EXISTS(SELECT c2.text
FROM         sysobjects o2 INNER JOIN
                      syscomments c2 ON c2.id = o2.id
where c2.id =c.id  AND   c2.colid=c.colid-1  AND (RIGHT(c2.text,3)= LEFT(@find2,3)))
))

OR ((@LenFind=4) AND (colid>1) AND (LEFT(c.text,2)= RIGHT(@find2,2)) AND (
EXISTS(SELECT c2.text
FROM         sysobjects o2 INNER JOIN
                      syscomments c2 ON c2.id = o2.id
where c2.id =c.id  AND   c2.colid=c.colid-1  AND (RIGHT(c2.text,2)= LEFT(@find2,2)))
))

OR ((@LenFind=4) AND (colid>1) AND (LEFT(c.text,3)= RIGHT(@find2,3)) AND (
EXISTS(SELECT c2.text
FROM         sysobjects o2 INNER JOIN
                      syscomments c2 ON c2.id = o2.id
where c2.id =c.id  AND   c2.colid=c.colid-1  AND (RIGHT(c2.text,1)= LEFT(@find2,1)))
))

--5 CHAR
OR ((@LenFind=5) AND (colid>1) AND (LEFT(c.text,1)= RIGHT(@find2,1)) AND (
EXISTS(SELECT c2.text
FROM         sysobjects o2 INNER JOIN
                      syscomments c2 ON c2.id = o2.id
where c2.id =c.id  AND   c2.colid=c.colid-1  AND (RIGHT(c2.text,4)= LEFT(@find2,4)))
))

OR ((@LenFind=5) AND (colid>1) AND (LEFT(c.text,2)= RIGHT(@find2,2)) AND (
EXISTS(SELECT c2.text
FROM         sysobjects o2 INNER JOIN
                      syscomments c2 ON c2.id = o2.id
where c2.id =c.id  AND   c2.colid=c.colid-1  AND (RIGHT(c2.text,3)= LEFT(@find2,3)))
))

OR ((@LenFind=5) AND (colid>1) AND (LEFT(c.text,3)= RIGHT(@find2,3)) AND (
EXISTS(SELECT c2.text
FROM         sysobjects o2 INNER JOIN
                      syscomments c2 ON c2.id = o2.id
where c2.id =c.id  AND   c2.colid=c.colid-1  AND (RIGHT(c2.text,2)= LEFT(@find2,2)))
))

OR ((@LenFind=5) AND (colid>1) AND (LEFT(c.text,4)= RIGHT(@find2,4)) AND (
EXISTS(SELECT c2.text
FROM         sysobjects o2 INNER JOIN
                      syscomments c2 ON c2.id = o2.id
where c2.id =c.id  AND   c2.colid=c.colid-1  AND (RIGHT(c2.text,1)= LEFT(@find2,1)))
))

--6 CHAR
OR ((@LenFind=6) AND (colid>1) AND (LEFT(c.text,1)= RIGHT(@find2,1)) AND (
EXISTS(SELECT c2.text
FROM         sysobjects o2 INNER JOIN
                      syscomments c2 ON c2.id = o2.id
where c2.id =c.id  AND   c2.colid=c.colid-1  AND (RIGHT(c2.text,5)= LEFT(@find2,5)))
))

OR ((@LenFind=6) AND (colid>1) AND (LEFT(c.text,2)= RIGHT(@find2,2)) AND (
EXISTS(SELECT c2.text
FROM         sysobjects o2 INNER JOIN
                      syscomments c2 ON c2.id = o2.id
where c2.id =c.id  AND   c2.colid=c.colid-1  AND (RIGHT(c2.text,4)= LEFT(@find2,4)))
))

OR ((@LenFind=6) AND (colid>1) AND (LEFT(c.text,3)= RIGHT(@find2,3)) AND (
EXISTS(SELECT c2.text
FROM         sysobjects o2 INNER JOIN
                      syscomments c2 ON c2.id = o2.id
where c2.id =c.id  AND   c2.colid=c.colid-1  AND (RIGHT(c2.text,3)= LEFT(@find2,3)))
))

OR ((@LenFind=6) AND (colid>1) AND (LEFT(c.text,4)= RIGHT(@find2,4)) AND (
EXISTS(SELECT c2.text
FROM         sysobjects o2 INNER JOIN
                      syscomments c2 ON c2.id = o2.id
where c2.id =c.id  AND   c2.colid=c.colid-1  AND (RIGHT(c2.text,2)= LEFT(@find2,2)))
))

OR ((@LenFind=6) AND (colid>1) AND (LEFT(c.text,5)= RIGHT(@find2,5)) AND (
EXISTS(SELECT c2.text
FROM         sysobjects o2 INNER JOIN
                      syscomments c2 ON c2.id = o2.id
where c2.id =c.id  AND   c2.colid=c.colid-1  AND (RIGHT(c2.text,1)= LEFT(@find2,1)))
))

--7 CHAR
OR ((@LenFind=7) AND (colid>1) AND (LEFT(c.text,1)= RIGHT(@find2,1)) AND (
EXISTS(SELECT c2.text
FROM         sysobjects o2 INNER JOIN
                      syscomments c2 ON c2.id = o2.id
where c2.id =c.id  AND   c2.colid=c.colid-1  AND (RIGHT(c2.text,6)= LEFT(@find2,6)))
))

OR ((@LenFind=7) AND (colid>1) AND (LEFT(c.text,2)= RIGHT(@find2,2)) AND (
EXISTS(SELECT c2.text
FROM         sysobjects o2 INNER JOIN
                      syscomments c2 ON c2.id = o2.id
where c2.id =c.id  AND   c2.colid=c.colid-1  AND (RIGHT(c2.text,5)= LEFT(@find2,5)))
))

OR ((@LenFind=7) AND (colid>1) AND (LEFT(c.text,3)= RIGHT(@find2,3)) AND (
EXISTS(SELECT c2.text
FROM         sysobjects o2 INNER JOIN
                      syscomments c2 ON c2.id = o2.id
where c2.id =c.id  AND   c2.colid=c.colid-1  AND (RIGHT(c2.text,4)= LEFT(@find2,4)))
))

OR ((@LenFind=7) AND (colid>1) AND (LEFT(c.text,4)= RIGHT(@find2,4)) AND (
EXISTS(SELECT c2.text
FROM         sysobjects o2 INNER JOIN
                      syscomments c2 ON c2.id = o2.id
where c2.id =c.id  AND   c2.colid=c.colid-1  AND (RIGHT(c2.text,3)= LEFT(@find2,3)))
))

OR ((@LenFind=7) AND (colid>1) AND (LEFT(c.text,5)= RIGHT(@find2,5)) AND (
EXISTS(SELECT c2.text
FROM         sysobjects o2 INNER JOIN
                      syscomments c2 ON c2.id = o2.id
where c2.id =c.id  AND   c2.colid=c.colid-1  AND (RIGHT(c2.text,2)= LEFT(@find2,2)))
))

OR ((@LenFind=7) AND (colid>1) AND (LEFT(c.text,6)= RIGHT(@find2,6)) AND (
EXISTS(SELECT c2.text
FROM         sysobjects o2 INNER JOIN
                      syscomments c2 ON c2.id = o2.id
where c2.id =c.id  AND   c2.colid=c.colid-1  AND (RIGHT(c2.text,1)= LEFT(@find2,1)))
))

--8 CHAR
OR ((@LenFind=8) AND (colid>1) AND (LEFT(c.text,1)= RIGHT(@find2,1)) AND (
EXISTS(SELECT c2.text
FROM         sysobjects o2 INNER JOIN
                      syscomments c2 ON c2.id = o2.id
where c2.id =c.id  AND   c2.colid=c.colid-1  AND (RIGHT(c2.text,7)= LEFT(@find2,7)))
))

OR ((@LenFind=8) AND (colid>1) AND (LEFT(c.text,2)= RIGHT(@find2,2)) AND (
EXISTS(SELECT c2.text
FROM         sysobjects o2 INNER JOIN
                      syscomments c2 ON c2.id = o2.id
where c2.id =c.id  AND   c2.colid=c.colid-1  AND (RIGHT(c2.text,6)= LEFT(@find2,6)))
))

OR ((@LenFind=8) AND (colid>1) AND (LEFT(c.text,3)= RIGHT(@find2,3)) AND (
EXISTS(SELECT c2.text
FROM         sysobjects o2 INNER JOIN
                      syscomments c2 ON c2.id = o2.id
where c2.id =c.id  AND   c2.colid=c.colid-1  AND (RIGHT(c2.text,5)= LEFT(@find2,5)))
))

OR ((@LenFind=8) AND (colid>1) AND (LEFT(c.text,4)= RIGHT(@find2,4)) AND (
EXISTS(SELECT c2.text
FROM         sysobjects o2 INNER JOIN
                      syscomments c2 ON c2.id = o2.id
where c2.id =c.id  AND   c2.colid=c.colid-1  AND (RIGHT(c2.text,4)= LEFT(@find2,4)))
))

OR ((@LenFind=8) AND (colid>1) AND (LEFT(c.text,5)= RIGHT(@find2,5)) AND (
EXISTS(SELECT c2.text
FROM         sysobjects o2 INNER JOIN
                      syscomments c2 ON c2.id = o2.id
where c2.id =c.id  AND   c2.colid=c.colid-1  AND (RIGHT(c2.text,3)= LEFT(@find2,3)))
))

OR ((@LenFind=8) AND (colid>1) AND (LEFT(c.text,6)= RIGHT(@find2,6)) AND (
EXISTS(SELECT c2.text
FROM         sysobjects o2 INNER JOIN
                      syscomments c2 ON c2.id = o2.id
where c2.id =c.id  AND   c2.colid=c.colid-1  AND (RIGHT(c2.text,2)= LEFT(@find2,2)))
))

OR ((@LenFind=8) AND (colid>1) AND (LEFT(c.text,7)= RIGHT(@find2,7)) AND (
EXISTS(SELECT c2.text
FROM         sysobjects o2 INNER JOIN
                      syscomments c2 ON c2.id = o2.id
where c2.id =c.id  AND   c2.colid=c.colid-1  AND (RIGHT(c2.text,1)= LEFT(@find2,1)))
))

--9 CHAR
OR ((@LenFind=9) AND (colid>1) AND (LEFT(c.text,1)= RIGHT(@find2,1)) AND (
EXISTS(SELECT c2.text
FROM         sysobjects o2 INNER JOIN
                      syscomments c2 ON c2.id = o2.id
where c2.id =c.id  AND   c2.colid=c.colid-1  AND (RIGHT(c2.text,8)= LEFT(@find2,8)))
))

OR ((@LenFind=9) AND (colid>1) AND (LEFT(c.text,2)= RIGHT(@find2,2)) AND (
EXISTS(SELECT c2.text
FROM         sysobjects o2 INNER JOIN
                      syscomments c2 ON c2.id = o2.id
where c2.id =c.id  AND   c2.colid=c.colid-1  AND (RIGHT(c2.text,7)= LEFT(@find2,7)))
))

OR ((@LenFind=9) AND (colid>1) AND (LEFT(c.text,3)= RIGHT(@find2,3)) AND (
EXISTS(SELECT c2.text
FROM         sysobjects o2 INNER JOIN
                      syscomments c2 ON c2.id = o2.id
where c2.id =c.id  AND   c2.colid=c.colid-1  AND (RIGHT(c2.text,6)= LEFT(@find2,6)))
))

OR ((@LenFind=9) AND (colid>1) AND (LEFT(c.text,4)= RIGHT(@find2,4)) AND (
EXISTS(SELECT c2.text
FROM         sysobjects o2 INNER JOIN
                      syscomments c2 ON c2.id = o2.id
where c2.id =c.id  AND   c2.colid=c.colid-1  AND (RIGHT(c2.text,5)= LEFT(@find2,5)))
))

OR ((@LenFind=9) AND (colid>1) AND (LEFT(c.text,5)= RIGHT(@find2,5)) AND (
EXISTS(SELECT c2.text
FROM         sysobjects o2 INNER JOIN
                      syscomments c2 ON c2.id = o2.id
where c2.id =c.id  AND   c2.colid=c.colid-1  AND (RIGHT(c2.text,4)= LEFT(@find2,4)))
))

OR ((@LenFind=9) AND (colid>1) AND (LEFT(c.text,6)= RIGHT(@find2,6)) AND (
EXISTS(SELECT c2.text
FROM         sysobjects o2 INNER JOIN
                      syscomments c2 ON c2.id = o2.id
where c2.id =c.id  AND   c2.colid=c.colid-1  AND (RIGHT(c2.text,3)= LEFT(@find2,3)))
))

OR ((@LenFind=9) AND (colid>1) AND (LEFT(c.text,7)= RIGHT(@find2,7)) AND (
EXISTS(SELECT c2.text
FROM         sysobjects o2 INNER JOIN
                      syscomments c2 ON c2.id = o2.id
where c2.id =c.id  AND   c2.colid=c.colid-1  AND (RIGHT(c2.text,2)= LEFT(@find2,2)))
))

OR ((@LenFind=9) AND (colid>1) AND (LEFT(c.text,8)= RIGHT(@find2,8)) AND (
EXISTS(SELECT c2.text
FROM         sysobjects o2 INNER JOIN
                      syscomments c2 ON c2.id = o2.id
where c2.id =c.id  AND   c2.colid=c.colid-1  AND (RIGHT(c2.text,1)= LEFT(@find2,1)))
))

--10 CHAR
OR ((@LenFind=10) AND (colid>1) AND (LEFT(c.text,1)= RIGHT(@find2,1)) AND (
EXISTS(SELECT c2.text
FROM         sysobjects o2 INNER JOIN
                      syscomments c2 ON c2.id = o2.id
where c2.id =c.id  AND   c2.colid=c.colid-1  AND (RIGHT(c2.text,9)= LEFT(@find2,9)))
))

OR ((@LenFind=10) AND (colid>1) AND (LEFT(c.text,2)= RIGHT(@find2,2)) AND (
EXISTS(SELECT c2.text
FROM         sysobjects o2 INNER JOIN
                      syscomments c2 ON c2.id = o2.id
where c2.id =c.id  AND   c2.colid=c.colid-1  AND (RIGHT(c2.text,8)= LEFT(@find2,8)))
))

OR ((@LenFind=10) AND (colid>1) AND (LEFT(c.text,3)= RIGHT(@find2,3)) AND (
EXISTS(SELECT c2.text
FROM         sysobjects o2 INNER JOIN
                      syscomments c2 ON c2.id = o2.id
where c2.id =c.id  AND   c2.colid=c.colid-1  AND (RIGHT(c2.text,7)= LEFT(@find2,7)))
))

OR ((@LenFind=10) AND (colid>1) AND (LEFT(c.text,4)= RIGHT(@find2,4)) AND (
EXISTS(SELECT c2.text
FROM         sysobjects o2 INNER JOIN
                      syscomments c2 ON c2.id = o2.id
where c2.id =c.id  AND   c2.colid=c.colid-1  AND (RIGHT(c2.text,6)= LEFT(@find2,6)))
))

OR ((@LenFind=10) AND (colid>1) AND (LEFT(c.text,5)= RIGHT(@find2,5)) AND (
EXISTS(SELECT c2.text
FROM         sysobjects o2 INNER JOIN
                      syscomments c2 ON c2.id = o2.id
where c2.id =c.id  AND   c2.colid=c.colid-1  AND (RIGHT(c2.text,5)= LEFT(@find2,5)))
))

OR ((@LenFind=10) AND (colid>1) AND (LEFT(c.text,6)= RIGHT(@find2,6)) AND (
EXISTS(SELECT c2.text
FROM         sysobjects o2 INNER JOIN
                      syscomments c2 ON c2.id = o2.id
where c2.id =c.id  AND   c2.colid=c.colid-1  AND (RIGHT(c2.text,4)= LEFT(@find2,4)))
))

OR ((@LenFind=10) AND (colid>1) AND (LEFT(c.text,7)= RIGHT(@find2,7)) AND (
EXISTS(SELECT c2.text
FROM         sysobjects o2 INNER JOIN
                      syscomments c2 ON c2.id = o2.id
where c2.id =c.id  AND   c2.colid=c.colid-1  AND (RIGHT(c2.text,3)= LEFT(@find2,3)))
))

OR ((@LenFind=10) AND (colid>1) AND (LEFT(c.text,8)= RIGHT(@find2,8)) AND (
EXISTS(SELECT c2.text
FROM         sysobjects o2 INNER JOIN
                      syscomments c2 ON c2.id = o2.id
where c2.id =c.id  AND   c2.colid=c.colid-1  AND (RIGHT(c2.text,2)= LEFT(@find2,2)))
))

OR ((@LenFind=10) AND (colid>1) AND (LEFT(c.text,9)= RIGHT(@find2,9)) AND (
EXISTS(SELECT c2.text
FROM         sysobjects o2 INNER JOIN
                      syscomments c2 ON c2.id = o2.id
where c2.id =c.id  AND   c2.colid=c.colid-1  AND (RIGHT(c2.text,1)= LEFT(@find2,1)))
))
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO


CREATE PROCEDURE zzzz  AS
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------1234567890---------------
print 123
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO


CREATE PROCEDURE zzzz2  AS
--1234567890

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

----------------test code

exec sp_FindStringInCode '1234567890', p
exec sp_FindStringInCode2 '1234567890', p

exec sp_FindStringInCode2 '56', p

exec sp_FindStringInCode2 '456', p
exec sp_FindStringInCode2 '567', p

exec sp_FindStringInCode2 '3456', p
exec sp_FindStringInCode2 '4567', p
exec sp_FindStringInCode2 '5678', p

exec sp_FindStringInCode2 '23456', p
exec sp_FindStringInCode2 '34567', p
exec sp_FindStringInCode2 '45678', p
exec sp_FindStringInCode2 '56789', p

exec sp_FindStringInCode2 '123456', p
exec sp_FindStringInCode2 '234567', p
exec sp_FindStringInCode2 '345678', p
exec sp_FindStringInCode2 '456789', p
exec sp_FindStringInCode2 '567890', p

exec sp_FindStringInCode2 '1234567', p
exec sp_FindStringInCode2 '2345678', p
exec sp_FindStringInCode2 '3456789', p
exec sp_FindStringInCode2 '4567890', p

exec sp_FindStringInCode2 '12345678', p
exec sp_FindStringInCode2 '23456789', p
exec sp_FindStringInCode2 '34567890', p

exec sp_FindStringInCode2 '123456789', p
exec sp_FindStringInCode2 '234567890', p

Rate

Share

Share

Rate