Find Stored Procedure Text (SQL Server)
Here are the primary methods you can use
Using the INFORMATION_SCHEMA Views
- Use the
ROUTINE_DEFINITION
column to search for the text you're interested in. - The
INFORMATION_SCHEMA.ROUTINES
view provides metadata about stored procedures, functions, and triggers.
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
- Combine these views to find procedures that directly reference or are referenced by objects containing the text.
sys.sp_depends
provides dependencies between objects.sys.procedures
contains information about stored procedures.
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)
- If you're working with an older version, you can use it:
- However, it's deprecated in newer SQL Server versions.
sys.syscomments
was used to store text for objects like stored procedures.
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
- Be aware that searching for text within comments might not be accurate, as comments aren't always parsed by the database engine.
- If you're searching for multiple words, use
AND
orOR
operators as needed. - For more complex searches, consider using full-text indexing or regular expressions.
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>%';
- LIKE '%<your_text>%';
This condition filters the results to only include routines where theROUTINE_DEFINITION
contains the specified text (<your_text>
). The%
wildcards match any number of characters before or after the text. - ROUTINE_DEFINITION
This column contains the actual definition of the routine, including its code. - ROUTINE_NAME
This column returns the name of the routine. - INFORMATION_SCHEMA.ROUTINES
This system view provides metadata about all routines (stored procedures, functions, and triggers) in the database.
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>%';
- 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>
)
- Depend on a specific object (
- JOIN
TheJOIN
keyword combines rows from both tables based on the specified condition. - sys.sp_depends
This system table shows dependencies between objects in the database. - sys.procedures
This system table contains metadata about stored procedures.
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>%';
- WHERE clauses
These conditions filter the results to include only stored procedures (o.type = 'P'
) that contain the specified text. - sys.objects
This system table contains metadata about database objects. - sys.syscomments
This system table (deprecated in newer SQL Server versions) stores text for various database objects.
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):
- Filter
You can filter the search results by name, type, or other criteria. - Search
Open SSMS, navigate to the "Object Explorer" pane, and search for stored procedures using the built-in search function.
Using sp_helptext:
- Usage
ReplaceEXEC sp_helptext 'your_procedure_name';
'your_procedure_name'
with the actual name of the procedure you're searching for. - Procedure Metadata
This system procedure returns the text of a specified stored procedure.
Using Dynamic SQL:
- 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;
- Dynamic Query
Construct a dynamic SQL query that searches for the specified text within theROUTINE_DEFINITION
column of theINFORMATION_SCHEMA.ROUTINES
view.
Using Full-Text Indexing:
- Search
Use full-text search functions (e.g.,CONTAINS
) to efficiently search for text within the index. - Index Creation
Create a full-text index on theROUTINE_DEFINITION
column of theINFORMATION_SCHEMA.ROUTINES
view.
Using Regular Expressions:
- Example
SELECT ROUTINE_NAME FROM INFORMATION_SCHEMA.ROUTINES WHERE PATINDEX('%[a-z]your_text[a-z]%', ROUTINE_DEFINITION) > 0;
- Pattern Matching
If you need more complex pattern matching, use regular expressions. SQL Server supports regular expressions through functions likePATINDEX
.
Choose the method that best suits your specific needs based on factors like
- Tool Preference
If you're comfortable using SSMS, the built-in search functionality can be a quick and easy solution. - Complexity
If you need complex pattern matching, regular expressions might be necessary. - Performance
For large databases, full-text indexing or dynamic SQL with optimized search parameters can be more efficient.
sql-server sql-server-2008 t-sql