SQL Search Code

DECLARE @string VARCHAR(255)
SET @string='Insert String You Are Searching'

/*Drop temp table if already exists in connection.*/
IF OBJECT_ID('tempdb..#data') IS NOT NULL DROP TABLE #data

/*Gather Data*/
SELECT 
  DISTINCT 
		CASE sysobjects.TYPE 
			WHEN 'P' THEN 'Stored Proc'
			WHEN 'V' THEN 'View'
			WHEN 'FN' THEN 'Function'
			WHEN 'TF' THEN 'Function'
			WHEN 'TR' THEN 'Trigger'
		END AS 'Object Type'
	,sysobjects.Name AS 'Object Name'
	,CASE 
		WHEN PATINDEX('%' + @String + '%',syscomments.text) >0 THEN 
				SUBSTRING(syscomments.text,PATINDEX('%' + @String + '%',syscomments.text),PATINDEX('%' + @String + '%',syscomments.text)+150) 
		ELSE '' 
	END AS 'PartialText'
INTO #data
FROM sysobjects (NOLOCK)
	INNER JOIN syscomments (NOLOCK) ON (syscomments.ID = sysobjects.ID)
WHERE 
	sysobjects.type in ('P', 'V', 'FN', 'TR', 'TF')  -- stored procs, views, functions, triggers
	AND (syscomments.text like '%' + @String + '%')

/*Results*/
SELECT
	*
	,CASE 
		WHEN PATINDEX('% %',PartialText) < PATINDEX('%'+CHAR(13)+'%',PartialText) 
			THEN LEFT(PartialText,PATINDEX('% %',PartialText)) 
		ELSE LEFT(PartialText,PATINDEX('%'+CHAR(13)+'%',PartialText)) 
	END AS tableReference
FROM #data
WHERE 
	PATINDEX('%DEPENDENC%',PartialText)=0