Search View & Routine-Definition

Search_Routines_and_Views.sql

DECLARE @likeStatement nvarchar(4000); 
SET @likeStatement = '%SearchText%'; 


SELECT 
	 isv.TABLE_SCHEMA
	,isv.TABLE_NAME
	,tDef.def 
FROM INFORMATION_SCHEMA.VIEWS AS isv 

OUTER APPLY 
	(
		SELECT OBJECT_DEFINITION(OBJECT_ID(isv.TABLE_SCHEMA + N'.' + isv.TABLE_NAME)) AS def 
	) AS tDef 

WHERE (1=1) 
AND def LIKE @likeStatement 


SELECT 
	 isr.SPECIFIC_SCHEMA 
	,isr.SPECIFIC_NAME 
	,isr.ROUTINE_TYPE 
	,isr.DATA_TYPE 
	,tDef.def 
FROM INFORMATION_SCHEMA.ROUTINES AS isr 

OUTER APPLY 
	(
		SELECT OBJECT_DEFINITION(OBJECT_ID(isr.SPECIFIC_SCHEMA + N'.' + isr.SPECIFIC_NAME)) AS def 
	) AS tDef 

WHERE (1=1) 
AND tDef.def LIKE @likeStatement 

ORDER BY ROUTINE_TYPE, CASE WHEN DATA_TYPE = 'TABLE' THEN 0 ELSE 1 END, SPECIFIC_SCHEMA, SPECIFIC_NAME