Search Text in all Procedure, View, Function, Table of sql server

Description : Below are queries for search text from all sql server procedure, table, view and function in single database

-- SEARCH TEXT IN ALL PROCEDURE OF SQL SERVER
SELECT OBJECT_NAME(OBJECT_ID) SP_Name, OBJECT_DEFINITION(OBJECT_ID) SP_Definition FROM sys.procedures
WHERE  OBJECT_DEFINITION(OBJECT_ID) LIKE '%'+'SEARCH TEXT'+'%'
   

-- SEARCH TEXT IN ALL VIEW OF SQL SERVER
SELECT OBJECT_NAME(OBJECT_ID) View_Name
,OBJECT_DEFINITION(OBJECT_ID) View_Definition
FROM   sys.views
WHERE  OBJECT_DEFINITION(OBJECT_ID) LIKE '%'+'SEARCH TEXT'+'%'

-- SEARCH TEXT IN ALL FUNCTION OF SQL SERVER
SELECT ROUTINE_NAME           Function_Name
,ROUTINE_DEFINITION     Function_definition
FROM   INFORMATION_SCHEMA.ROUTINES
WHERE  ROUTINE_DEFINITION LIKE '%'+'SEARCH TEXT'+'%'
AND ROUTINE_TYPE = 'FUNCTION'
ORDER BY ROUTINE_NAME

-- SEARCH TEXT IN ALL TABLE OF SQL SERVER
SELECT t.name      AS Table_Name
,c.name      AS COLUMN_NAME
FROM   sys.tables  AS t
INNER JOIN sys.columns c
ON  t.OBJECT_ID = c.OBJECT_ID
WHERE  c.name LIKE '%'+'SEARCH TEXT'+'%'
ORDER BY
Table_Name

SQL DATALENGTH() Function

Return length of text given in function

Example

SELECT DATALENGTH('Text');

RESULT : 4

SQL CONCAT() Function

Join two or more string togathet

Example

SELECT CONCAT('Text1', '-2');

RESULT : Text1-2

SQL CHARINDEX() Function

Return character index of text

Example

SELECT CHARINDEX('x', 'Text')

RESULT : 3