Sentry Answers>SQL Server>

How do I search text in stored procedures in SQL Server?

How do I search text in stored procedures in SQL Server?

Richard C.

—

The Problem

In SQL Server, you might have hundreds of stored procedures. How do you search the text of all of them to discover which uses a table, column, or string that you want to find?

The Solution

Let’s create a sample stored procedure to demonstrate how to search its text:

Click to Copy
CREATE PROCEDURE HelloWorld
AS
BEGIN
    PRINT 'Hello, [World!';
END;

To search all our stored procedures for [World, we can run:

Click to Copy
SELECT
    OBJECT_NAME(object_id) as Name,
    OBJECT_DEFINITION(object_id) as Body
FROM
    sys.procedures
WHERE
    OBJECT_DEFINITION(object_id) LIKE '%[[]world%'

-- Name |           Body |
-- ----------------------------------+
-- HelloWorld|      CREATE PROCEDURE HelloWorld¶AS¶BEGIN¶    PRINT 'Hello, [World!';¶END;|

Note that to search for [ you have to enclose it in [[] because brackets usually encase table and column names in SQL Server. Depending on the text you are looking for you might want to try excluding the brackets, including them normally, or specifying your escape sequence explicitly with LIKE '%\[world%' ESCAPE '\'.

Do not try to search your stored procedures using INFORMATION_SCHEMA.ROUTINES. That searches only the first 4000 characters of the procedure. Rather use sys.procedures, as shown above.

  • Syntax.fmListen to the Syntax Podcast
  • Community SeriesIdentify, Trace, and Fix Endpoint Regression Issues
  • Syntax.fm logo
    Listen to the Syntax Podcast

    Tasty treats for web developers brought to you by Sentry. Get tips and tricks from Wes Bos and Scott Tolinski.

    SEE EPISODES

Considered “not bad” by 4 million developers and more than 100,000 organizations worldwide, Sentry provides code-level observability to many of the world’s best-known companies like Disney, Peloton, Cloudflare, Eventbrite, Slack, Supercell, and Rockstar Games. Each month we process billions of exceptions from the most popular products on the internet.

learn more about sentryjoin our discord
© 2024 • Sentry is a registered Trademark of Functional Software, Inc.