In SQL Server, you can search for specific phrases or commands within the definitions of stored procedures, views, triggers, and other database objects by querying the sys.sql_modules system view or the syscomments table. However, it's important to note that syscomments is deprecated, and using sys.sql_modules is the recommended approach.

To search for the phrase "Insert into ExecutiveMaster"(put here you table name or you can put your text which you want to search) in your database objects, you can use the following SQL query:

Using sys.sql_modules

sql
1SELECT 
2    o.name AS ObjectName,
3    o.type_desc AS ObjectType,
4    m.definition AS ObjectDefinition
5FROM 
6    sys.sql_modules m
7JOIN 
8    sys.objects o ON m.object_id = o.object_id
9WHERE 
10    m.definition LIKE '%Insert into ExecutiveMaster%'
11ORDER BY 
12    o.type_desc, o.name;

Explanation of the Query:

1. SELECT Clause: This part selects the object name, object type, and the definition of the object.

   • o.name: The name of the database object (e.g., stored procedure, view).
   • o.type_desc: The type of the object (e.g., SQL_STORED_PROCEDURE, SQL_VIEW).
   • m.definition: The actual SQL definition of the object.

2. FROM Clause: This specifies the tables/views from which to retrieve the data.

   • sys.sql_modules m: This view contains the definitions of SQL Server objects.
   • sys.objects o: This view contains a row for each object that is created within a database.

3. JOIN Clause: This joins the sys.sql_modules view with the sys.objects view on the object_id to get the object names and types.

4. WHERE Clause: This filters the results to only include those definitions that contain the specified phrase.

   • m.definition LIKE '%Insert into ExecutiveMaster%': This checks if the definition contains the phrase "Insert into ExecutiveMaster".

5. ORDER BY Clause: This orders the results by object type and name for better readability.

Note:

• If you are using an older version of SQL Server that still supports syscomments, you could use a similar query, but it is advisable to use sys.sql_modules for better compatibility with newer versions.
• The LIKE operator is case-insensitive by default in SQL Server, but this can depend on the collation settings of your database.