Multiple Methods for Finding Stored Procedures by Name in SQL Server

System View Query Methods

In SQL Server database management practice, quickly locating specific stored procedures represents a common requirement. This objective can be efficiently achieved through system catalog views. The most fundamental query approach utilizes the sys.procedures system view, which contains basic information about all stored procedures in the current database.

The following query example demonstrates how to perform fuzzy matching based on stored procedure names:

SELECT * 
FROM sys.procedures 
WHERE name LIKE '%name_of_proc%'

This query returns metadata for all stored procedures whose names contain the specified string, including crucial information such as object ID, creation date, and modification date. The LIKE operator supports wildcard matching, where % represents any sequence of characters and _ represents a single character.

Retrieving Stored Procedure Definition Code

When examining the specific implementation code of stored procedures becomes necessary, queries can incorporate the syscomments table. The syscomments table stores definition texts for database objects including stored procedures and functions.

The following query illustrates how to obtain definitions for stored procedures matching specific name patterns:

SELECT text 
FROM syscomments c
    INNER JOIN sys.procedures p ON p.object_id = c.object_id
WHERE p.name LIKE '%name_of_proc%'

This query associates the two system views through object ID, ensuring only the definition text of target stored procedures is returned. Note that for lengthy stored procedure definitions, the text might be segmented across multiple rows, requiring ordering by colid to obtain the complete definition.

ANSI Standard Compatible Method

To maintain compatibility across database systems, SQL Server provides the ANSI SQL-standard compliant INFORMATION_SCHEMA.ROUTINES view. This view presents stored procedure and function information in a standardized format.

A query example using the ANSI standard method follows:

SELECT * 
FROM INFORMATION_SCHEMA.ROUTINES 
WHERE ROUTINE_NAME LIKE '%name_of_proc%'

This approach proves particularly suitable for scenarios involving code migration between different database systems, as it adheres to universal information schema standards. Returned results include standardized fields such as stored procedure name, schema, and definition language.

Graphical Interface Operation

Beyond SQL query methods, SQL Server Management Studio offers intuitive graphical interface operations. Within the Object Explorer Details window (accessible by pressing F7), users can apply filters to quickly locate stored procedures.

Operation steps include: clicking the filter button in the stored procedure list interface, entering complete or partial stored procedure names, with the system automatically filtering to display matching results. This method suits users unfamiliar with SQL syntax or scenarios requiring quick browsing.

Advanced Search Techniques

In practical development work, searching based on stored procedure content rather than merely names frequently becomes necessary. The referenced article mentions advanced techniques achieving this functionality through custom stored procedures.

The following example creates a specialized utility procedure for searching stored procedure content:

CREATE PROCEDURE dbo.Find_InSP @string nvarchar(max)
AS
BEGIN
    SET NOCOUNT ON;
    SELECT cmd = N'EXEC sys.sp_helptext ''' + 
        QUOTENAME(OBJECT_SCHEMA_NAME([object_id])) + '.' + 
        QUOTENAME([name]) + ''';'
    FROM sys.procedures 
    WHERE OBJECT_DEFINITION([object_id]) LIKE N'%' + @string + '%'
    ORDER BY cmd;
END

This custom stored procedure uses the OBJECT_DEFINITION function to directly access stored procedure definition text, supporting searches for arbitrary strings within stored procedure code. Returned results also include commands for invoking sp_helptext to view complete definitions, significantly enhancing development efficiency.

Similarly, a version searching sys.sql_modules can be created to encompass other database objects like functions and triggers:

CREATE PROCEDURE dbo.Find_InModule @string nvarchar(max)
AS
BEGIN
    SET NOCOUNT ON;
    SELECT cmd = N'EXEC sys.sp_helptext ''' + 
        QUOTENAME(OBJECT_SCHEMA_NAME([object_id])) + '.' + 
        QUOTENAME(OBJECT_NAME([object_id])) + ''';'
    FROM sys.sql_modules 
    WHERE [definition] LIKE N'%' + @string + '%'
    ORDER BY cmd;
END

These advanced techniques prove especially valuable for code auditing, impact analysis, or refactoring work within large database systems.

Performance Considerations and Best Practices

When employing these search methods, attention to performance impact remains essential. Fuzzy searches based on LIKE might perform slowly on large databases, particularly when using leading wildcards %string that prevent index utilization.

Recommendations for production environments include: establishing maintenance plans to regularly update statistics for frequently used search patterns; validating query performance in test environments before deployment to critical business databases; combining SET STATISTICS TIME ON and SET STATISTICS IO ON to monitor query overhead.

Additionally, proper handling of schema-qualified names and special characters proves crucial. Using the QUOTENAME function enables safe processing of object names containing special characters, avoiding SQL injection risks.