Search for text within procedures, views and functions

Observed sign or problem

We need to know where the name of a specific table, column or call appears. Manually reviewing each stored procedure can hide just the reference that explains a behavior.

What we need to distinguish

A text search locates characters without interpreting their role. A dependency, in contrast, records that one object’s definition references another.

This is a textual search, not a dependency analysis. You can find a word in code, comments or strings. Its usefulness is in reducing the set of objects that we will later review with context.

Query

DECLARE @Texto NVARCHAR(200) = N'Producto';

SELECT
    s.name AS Esquema,
    o.name AS Objeto,
    o.type_desc AS Tipo,
    p.Posicion,
    SUBSTRING(
        m.definition,
        CASE WHEN p.Posicion > 120 THEN p.Posicion - 120 ELSE 1 END,
        500
    ) AS Fragmento
FROM sys.sql_modules AS m
INNER JOIN sys.objects AS o
    ON o.object_id = m.object_id
INNER JOIN sys.schemas AS s
    ON s.schema_id = o.schema_id
CROSS APPLY
(
    VALUES (CHARINDEX(@Texto, m.definition))
) AS p (Posicion)
WHERE o.type IN ('P', 'V', 'FN', 'IF', 'TF', 'TR')
  AND m.definition IS NOT NULL
  AND p.Posicion > 0
ORDER BY
    o.type_desc,
    s.name,
    o.name;

The query returns the first position found and a nearby fragment. This allows you to decide which objects deserve a full read without filling the result with extensive definitions.

Parameters or permissions

Collation is the set of rules that determines how text is compared and sorted. An encrypted module hides its definition and may return NULL, the marker for an absent or unknown value.

Change @Texto to a recognizable identifier or fragment. The comparison of CHARINDEX follows the collation of the expression. If you need a case-sensitive comparison, you can explicitly apply a collation compatible with your environment.

The definition of an encrypted module appears as NULL. Permission is also required to view the corresponding definition; Having execute permission does not always mean being able to read your text.

How to read the result

An alias is an alternative name used inside a query. Dynamic SQL is SQL code built during execution.

Tipo distinguishes procedures, views, functions and triggers. Fragmento provides enough context to separate a real reference from an accidental match.

If you search for Producto, you can also find ProductoHistorico, an alias, a comment, or the text of a message. Open the full definition before concluding how to use it:

SELECT OBJECT_DEFINITION(OBJECT_ID(N'dbo.usp_ConsultarProducto'));

A match within dynamic SQL may be relevant even if it does not appear in normal dependencies. On the contrary, a name constructed by concatenation may not contain the complete text you searched for.

What this does establish

Allows you to state that the text appears in the visible definition of certain modules in the current database. It also helps form an initial list of objects for inspection.

What this still does not establish

It does not prove that the fragment is executed, that it corresponds to the same object you are investigating, or that the reference is valid. It may be in a comment, an unreachable branch, or a chain constructed for another purpose.

An empty result also does not discard fragmented dynamic SQL, encrypted modules, application code, jobs, external packages, or objects without visibility.

Next reasonable check

Read the full definition of each candidate and query object dependencies to compare the textual match with the recorded relationships. If the reference comes from an application, its code, configuration and executed calls will also have to be reviewed.

Search SQL Server