Find Stored Procedure Text (SQL Server)

Here are the primary methods you can use:

Using the INFORMATION_SCHEMA Views:

• The INFORMATION_SCHEMA.ROUTINES view provides metadata about stored procedures, functions, and triggers.
• Use the ROUTINE_DEFINITION column to search for the text you're interested in.

SELECT ROUTINE_NAME, ROUTINE_DEFINITION
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_DEFINITION LIKE '%<your_text>%';

Replace <your_text> with the actual text you're searching for.

Using sys.procedures and sys.sp_depends:

• sys.procedures contains information about stored procedures.
• sys.sp_depends provides dependencies between objects.
• Combine these views to find procedures that directly reference or are referenced by objects containing the text.

SELECT p.name AS ProcedureName, p.definition
FROM sys.procedures p
JOIN sys.sp_depends d ON p.object_id = d.depender_id
WHERE d.depender_id = OBJECT_ID('<object_name>')
  AND p.definition LIKE '%<your_text>%';

Replace <object_name> with the name of an object you know contains the text, and replace <your_text> with the search term.

Using sys.syscomments (Deprecated):

• sys.syscomments was used to store text for objects like stored procedures.
• However, it's deprecated in newer SQL Server versions.
• If you're working with an older version, you can use it:

SELECT c.text
FROM sys.syscomments c
JOIN sys.objects o ON c.id = o.object_id
WHERE o.type = 'P'
  AND c.text LIKE '%<your_text>%';

Additional Tips:

• For more complex searches, consider using full-text indexing or regular expressions.
• If you're searching for multiple words, use AND or OR operators as needed.
• Be aware that searching for text within comments might not be accurate, as comments aren't always parsed by the database engine.

Understanding the Code Examples

Prompt:

Please explain in English the example codes for programming related to "How do I find a stored procedure containing <text>?" and "Find Stored Procedure Text (SQL Server)".

Response:

The code examples you've provided are SQL queries designed to search for stored procedures within a SQL Server database that contain a specific text string. Let's break down the components of these queries:

Using INFORMATION_SCHEMA.ROUTINES

SELECT ROUTINE_NAME, ROUTINE_DEFINITION
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_DEFINITION LIKE '%<your_text>%';

• INFORMATION_SCHEMA.ROUTINES: This system view provides metadata about all routines (stored procedures, functions, and triggers) in the database.
• ROUTINE_NAME: This column returns the name of the routine.
• ROUTINE_DEFINITION: This column contains the actual definition of the routine, including its code.
• LIKE '%<your_text>%';: This condition filters the results to only include routines where the ROUTINE_DEFINITION contains the specified text (<your_text>). The % wildcards match any number of characters before or after the text.

SELECT p.name AS ProcedureName, p.definition
FROM sys.procedures p
JOIN sys.sp_depends d ON p.object_id = d.depender_id
WHERE d.depender_id = OBJECT_ID('<object_name>')
  AND p.definition LIKE '%<your_text>%';

• sys.procedures: This system table contains metadata about stored procedures.
• sys.sp_depends: This system table shows dependencies between objects in the database.
• JOIN: The JOIN keyword combines rows from both tables based on the specified condition.
• WHERE clauses: These conditions filter the results to include only procedures that:
  • Depend on a specific object (<object_name>)
  • Contain the specified text (<your_text>)

SELECT c.text
FROM sys.syscomments c
JOIN sys.objects o ON c.id = o.object_id
WHERE o.type = 'P'
  AND c.text LIKE '%<your_text>%';

• sys.syscomments: This system table (deprecated in newer SQL Server versions) stores text for various database objects.
• sys.objects: This system table contains metadata about database objects.
• WHERE clauses: These conditions filter the results to include only stored procedures (o.type = 'P') that contain the specified text.

Alternative Methods for Finding Stored Procedures Containing Text

While the methods provided earlier are effective, here are some alternative approaches you might consider:

Using SQL Server Management Studio (SSMS):

• Search: Open SSMS, navigate to the "Object Explorer" pane, and search for stored procedures using the built-in search function.
• Filter: You can filter the search results by name, type, or other criteria.

Using sp_helptext:

• Procedure Metadata: This system procedure returns the text of a specified stored procedure.
• Usage:

  EXEC sp_helptext 'your_procedure_name';
  

  Replace 'your_procedure_name' with the actual name of the procedure you're searching for.

Using Dynamic SQL:

• Dynamic Query: Construct a dynamic SQL query that searches for the specified text within the ROUTINE_DEFINITION column of the INFORMATION_SCHEMA.ROUTINES view.
• Example:

  DECLARE @SearchText NVARCHAR(MAX) = 'your_text';
  DECLARE @SQL NVARCHAR(MAX) = 'SELECT ROUTINE_NAME FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_DEFINITION LIKE ''%''' + @SearchText + '''%''';
  EXEC sp_executesql @SQL;
  

Using Full-Text Indexing:

• Index Creation: Create a full-text index on the ROUTINE_DEFINITION column of the INFORMATION_SCHEMA.ROUTINES view.
• Search: Use full-text search functions (e.g., CONTAINS) to efficiently search for text within the index.

Using Regular Expressions:

• Pattern Matching: If you need more complex pattern matching, use regular expressions. SQL Server supports regular expressions through functions like PATINDEX.
• Example:

  SELECT ROUTINE_NAME
  FROM INFORMATION_SCHEMA.ROUTINES
  WHERE PATINDEX('%[a-z]your_text[a-z]%', ROUTINE_DEFINITION) > 0;
  

Choose the method that best suits your specific needs based on factors like:

• Performance: For large databases, full-text indexing or dynamic SQL with optimized search parameters can be more efficient.
• Complexity: If you need complex pattern matching, regular expressions might be necessary.
• Tool Preference: If you're comfortable using SSMS, the built-in search functionality can be a quick and easy solution.