Find Specific Text String In Stored Procedures, Functions, Views And Triggers In SQL Server

In this article, you will learn how to find columns or text in Store procedures, Functions, Views, and Triggers.

Suppose there are multiple numbers of Store procedures, Functions, Views, and Triggers that contain a particular column or specific text and you have to find it. Here are the solutions for it.

Now let’s get those records using SQL queries.

The below code will give you all the Store procedures, Functions, Views, and Triggers that contain the column which you mention.

FIND COLUMN NAME 

1. Search in All Objects

• This script searches your column name in stored procedures, views, functions as well other objects.

SELECT
  OBJECT_NAME(OBJECT_ID),
  definition
FROM sys.sql_modules
WHERE definition LIKE '%' + 'Your Column Name' + '%'

2. Search in Stored Procedure

• This script searches your column name only in stored procedures.

SELECT DISTINCT
  OBJECT_NAME(OBJECT_ID),
  OBJECT_DEFINITION(OBJECT_ID)
FROM sys.Procedures
WHERE OBJECT_DEFINITION(OBJECT_ID) LIKE '%' + 'Your Column Name' + '%'

The below code will give you all the Store procedures, Functions, Views, and Triggers that contain specific Text which you mention in the place of @FindString.

FIND STRING IN ALL PROCEDURES

• This script searches the specified text in the stored procedures.

SELECT
  OBJECT_NAME(OBJECT_ID) SP_Name,
  OBJECT_DEFINITION(OBJECT_ID) SP_Definition
FROM sys.procedures
WHERE OBJECT_DEFINITION(OBJECT_ID) LIKE '%' + 'FindString' + '%'

FIND STRING IN ALL VIEWS

• This script searches the specified text in all the Views.

SELECT
  OBJECT_NAME(OBJECT_ID) View_Name,
  OBJECT_DEFINITION(OBJECT_ID) View_Definition
FROM sys.views
WHERE OBJECT_DEFINITION(OBJECT_ID) LIKE '%' + 'FindString' + '%'

FIND STRING IN ALL FUNCTION

• This script searches the specified text in all the Function.

SELECT
  ROUTINE_NAME Function_Name,
  ROUTINE_DEFINITION Function_definition
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_DEFINITION LIKE '%' + 'FindString' + '%'
AND ROUTINE_TYPE = 'FUNCTION'
ORDER BY ROUTINE_NAME

FIND STRING IN ALL TABLES OF DATABASE.

• This script searches the specified text in all the Tables of the particular database.

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 '%' + 'FindString' + '%'
ORDER BY Table_Name

if you want to know about shortcut keys in SQL then please refer to this Shortcut Keys For SQL Server