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

SQL CHAR() Function

Return character given in function

Example

SELECT CHAR(97)

RESULT : a

SQL ASCII() Function

Return ascii value of given in function

Example

SELECT ASCII('a')

RESULT : 97

How to use SQL ALTER Table

- ALTER TABLE ADD COLUMN

Syntax

ALTER TABLE TableName ADD Column ColumnName DataType

Example

ALTER TABLE CityMaster ADD IsActibe Bit

- ALTER TABLE DROP COLUMN

Syntax

ALTER TABLE TableName DROP COLUMN ColumnName

Example

ALTER TABLE CityMaster DROP IsActive