Find a string in Procedures, Triggers, etc

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