SQL Server find text in stored procedure

In this SQL Server tutorial, we will discuss find text in a SQL Server stored procedure. Here we will learn How to find a specific text string in a SQL Server stored procedure. And we will also discuss the following list of topics.

• SQL Server find text in stored procedure
• SQL Server look for text in stored procedures
• SQL Server management studio find text in stored procedures
• Find and replace text in sql server stored procedure
• How to find a specific text string in a sql server stored procedure
• How to find and replace text in all stored procedures sql server
• How to find stored procedure containing text in sql server

SQL Server find text in stored procedure

Many times when we have a large number of stored procedures in our database. Then finding the exact stored procedure could be very difficult. In such cases, we should be aware of some way to find a stored procedure with some specific string or keywords. So, we can use the exact procedure that we want.

In this section, we discuss how to find some text (string) in a stored procedure in SQL Server. As in SQL Server, we cannot get the specific text from the procedure as a result.

We can only get the procedure name and its definition which consists of the given text. Due to this, the process is similar to how we search a stored procedure consisting of a specific text.

Now, in SQL Server, there are many ways to find a stored procedure containing a specific string or text. And we will try to cover some of the standard ways for this implementation.

Using sys.procedures

The first and the simplest way to find a stored procedure with a specific text is by using the sys.procedures.

The sys.procedures is a system view in SQL Server which stores data related to objects which are procedures in some way in SQL Server.

And we can easily query this view using the SELECT statement. Now, to find a specific text in the procedure, we have to use the LIKE operator. Here is the general syntax that we can use for this implementation.

USE database
GO

SELECT Name, OBJECT_DEFINITION(object_id) AS [Definition]
FROM sys.procedures
WHERE OBJECT_DEFINITION(OBJECT_ID) LIKE '%YOUR_TEXT%'
GO

• In the above syntax, first, we need to select the database in which we are looking for the procedure.
• After this, we are using the SELECT statement to select 2 columns from the system view. The first is the name of the object, and the second is the object definition.
• After this, we are using the WHERE statement and LIKE operator to find the given text.
• So, the above query will return the procedure name and its definition, if the given text is found in the procedure definition.

Let’s use this syntax to find the stored procedures with the specified text. And the SQL query for this implementation is given below.

USE [sqlserverguides]
GO

SELECT Name, OBJECT_DEFINITION(object_id) AS [Definition]
FROM sys.procedures
WHERE OBJECT_DEFINITION(OBJECT_ID) LIKE '%Student%'
GO

In the above example, we are trying to find the stored procedures which use the “Student” table in their definition. And the output for this query is as follows.

Read View in SQL Server

Using sys.sys_modules

The second way to find some text from the stored procedure definition is by using sys.sys_modules. In SQL Server, sys.sys_modules is also a system view that returns a row to each object in SQL Server including stored procedures.

Now, we can query this catalog view using the SELECT statement.

Let’s understand how to use this catalog view to find the text from a stored procedure. And the example related to its demonstration is given below.

SELECT
    object_id AS [ID], 
    OBJECT_NAME(object_id) AS [Procedure Name], 
    definition as [Definition]
FROM
    sys.sql_modules
WHERE
    OBJECTPROPERTY(object_id, 'IsProcedure') = 1 AND
    definition LIKE '%Employee%'

In the above example, we are trying to find the stored procedures which consist of the “Employee” text in the definition. For this, we are using the WHERE clause and LIKE operator.

In the end, we will get the following result as an output.

Read: How to get list of users in SQL Server

SQL Server management studio find text in stored procedures

Till now, we were only discussing the use of SQL queries to find text in stored procedures. But, we can also use SQL Server Management Studio for the implementation of this task.

The SQL Server Management Studio provides a filter option to filter the list of procedures. But, unfortunately, we cannot perform a search in the definition of multiple procedures at once. But, we can search procedures by names using some character or string.

Method-1

The steps for this implementation are given below.

• First, start the SQL Server Management Studio and move to the “Object Explorer” section.
• In Object Explorer, expand the database which consists of the stored procedures, and then, expands the “Programmability” directory.
• Under Programmability, right-click the “Stored Procedures” directory and then click on “Filter” > “Filter Settings“. It will open a new Filter Settings window.

• In the new window, select the “Contains” operator for the “Name” property and then, provide the value for it in the value column.

• After specifying the value, click on “OK” and we will get the filtered result under the Stored Procedures directory.

Method-2

Now, using SQL Server Management Studio, we can find some text from a specific stored procedure. For this task, we can follow the following steps in SSMS.

• From the Object Explorer, right-click the stored procedure that you want and select “Script Stored Procedure As“>”Alter To“>”New Query Editor Window“.

• By implementing the above step, it will open the script of the stored procedure in a new window.
• Next, click on the “Edit” option from the menu bar and click on “Find and Replace” and then, click the “Quick Find” option. Or we can also use the “Ctrl + F” shortcut. This will open a search bar on the query editor page.

• Now, in the search bar, we need to specify the text that we want to search. And if the text is available in the procedure definition then, the text will get highlighted.

Read: How to check if SQL Server is running

Find and replace text in sql server stored procedure

In this section, we will understand how to find and replace a text in a SQL Server stored procedure. Now, mainly there are 2 ways to implement this task. The first way is by using SQL Server Management Studio and the second is by executing Transact-SQL script.

Using SQL Server Management Studio

Here are some of the steps that we can use in SQL Server Management Studio to find and replace a text from a stored procedure.

• From the Object Explorer, right-click the stored procedure that you want and select “Script Stored Procedure As“>”Alter To“>”New Query Editor Window“.

• By implementing the above step, it will open the script of the stored procedure in a new window.
• Next, click on the “Edit” option from the menu bar and click on “Find and Replace” and then, click the “Quick Replace” option. This will open a find and replace bar on the query editor page.

• Now, in the find and replace bar, first, we need to specify the text that we want to find. After this, we also need to specify the alternative text in the replace bar. In the end, click “Enter” to perform the action.

Read SQL Server function return table

Using Transact-SQL

Now, let’s understand how we can perform this find and replace operation using the Transact-SQL query. For the execution of this task, first, we need to find the procedure which holds the given text.

And to find the procedure, we can use the sys.sysobjects system view. This view holds data related to each object in the database.

Let’s first see how to find the stored procedure using sys.sysobjects. The example for this implementation is shown below.

USE sqlserverguides
GO

DECLARE @original VARCHAR(MAX)

SELECT @original = OBJECT_DEFINITION(id)
FROM sys.sysobjects
WHERE xtype = 'P' AND 
OBJECT_DEFINITION(id) LIKE '%VARCHAR(20)%' 

PRINT @original

In the above example, we are using the SELECT statement to select the definition of the stored procedure which consists of the “VARCHAR(20)“. And then, we are storing the definition value as a string in a variable name @original.

In the end, we are using the PRINT statement to print the original definition. Here is the output of the query.

So, after finding the text in a stored procedure it’s time to understand how to replace that text with a new one. To replace the text, we can use the REPLACE() function in SQL Server. Let’s understand this by implementing the following example.

USE sqlserverguides
GO

DECLARE @updated VARCHAR(MAX)

SELECT @updated = REPLACE(REPLACE(OBJECT_DEFINITION(id),'VARCHAR(20)', 'VARCHAR(MAX)'), 'CREATE','ALTER')
FROM sys.sysobjects
WHERE xtype = 'P' AND 
OBJECT_DEFINITION(id) LIKE '%VARCHAR(20)%' 

PRINT @updated

In the above example, we are using the same sys.sysobjects to find the text. After this, we are using REPLACE() function 2 times in the statement.

First to replace the old text with a new one and then, the second one is used to replace the CREATE keyword with ALTER. In the end, we are using the PRINT statement to print the updated query.

Here is the output of the updated text query in SQL Server.

Note: This method is efficient only when you are detailing with one stored procedure. If there are multiple stored procedures then, the script will work for only the first found procedure.

Read: SQL Server check user permissions on table

How to find stored procedure containing text in SQL Server

In SQL Server, there ate many methods to find a stored procedure. One of the ways to search is by searching to text that the procedure may contain in its definition.

We have already created a dedicated tutorial on “Stored procedure for search functionality in SQL Server“. In this tutorial, we have covered how to find a stored procedure containing some given text in SQL Server. You can read that tutorial for more information.

How to find and replace text in all stored procedures sql server

In this section, we will discuss how to find and replace a specific text from all the stored procedures in SQL Server which carry that text.

Now, in SQL Server, there can be different ways to implement this specified task. But, in this section, we will discuss one standard method for this implementation in SQL Server.

The first step in this approach is to use a query to find the stored procedure which has the specified text in its definition. After this, we need to replace that text with a new text in the procedure definition. And generate a new query with updated text.

For this execution, first, we will find the procedure using sys.sysobjects in SQL Server. The sys.sysobjects is a system catalog view which stores data related to each object created within the database including stored procedures. Next, to replace the text with a new one, we will use the REPLACE() function in SQL Server.

For more details and examples related to REPLACE() function, you can also refer to the following tutorial “SQL Server Replace Function“.

Now, let’s understand the whole implementation with the help of an example. For this example, we will search for “dbo.Employees” and replace it with “dbo. EmployeeTable“.

This operation will be performed for all the stored procedures in the sqlserverguides database. The query to this example is illustrated below.

USE sqlserverguides
GO

SELECT Name AS [Procedure Name], 
       OBJECT_DEFINITION(id) AS [Original Definition],
       REPLACE(OBJECT_DEFINITION(id),'dbo.Employees', 'dbo.EmployeeTable') AS [Updated Definition]
FROM sys.sysobjects
WHERE xtype = 'P' AND 
OBJECT_DEFINITION(id) LIKE '%dbo.Employees%' 

In the above query, we have used the WHERE clause and LIKE operator to first find the names and definitions of the stored procedures which carry “dbo.Employees“. After this, we are using REPLACE() function to replace “dbo.Employees” with “dbo.EmployeeTable“.

So, the query will return a new column with an updated stored procedure definition.

Note: By using the above query, we are just getting a resultset which have original and updated stored procedure definition. We are not making any modifications in the definition because updating multiple stored procedures at once is not a good option.

You may also like to read the following SQL Server tutorials.

• Loop in SQL Server stored procedure
• Could not find stored procedure in SQL Server
• SQL Server stored procedure modified date
• Stored procedure for search functionality in SQL Server
• Try catch in SQL Server stored procedure 
• SQL Server stored procedure parameters

In this SQL Server tutorial, we have discussed How to find a specific text string in a SQL Server stored procedure. And we have also covered the following list of topics.

• SQL Server find text in stored procedure
• SQL Server look for text in stored procedures
• SQL Server management studio find text in stored procedures
• Find and replace text in sql server stored procedure
• How to find a specific text string in a sql server stored procedure
• How to find and replace text in all stored procedures sql server
• How to find stored procedure containing text in sql server