Answers by Sentry

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:

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

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

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.

Loved by over 4 million developers and more than 90,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
Share on Twitter
Bookmark this page
Ask a questionJoin the discussion

Related Answers

A better experience for your users. An easier life for your developers.

Try Sentry For FreeRequest a Demo
    TwitterGitHubDribbbleLinkedinDiscord
© 2024 • Sentry is a registered Trademark
of Functional Software, Inc.