How to find all Stored Procedures having a given text inside

How to find all Stored Procedures having a given text inside

If you want to search for all stored procedures having a given text inside, you may use the following script:

SELECT ROUTINE_NAME, ROUTINE_DEFINITION
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_DEFINITION LIKE '%__CS_%'
AND ROUTINE_TYPE='PROCEDURE'

This is the equivalent of running the following query directly against the system tables/views:

SELECT sys.sysobjects.name, sys.syscomments.text
FROM sys.sysobjects INNER JOIN syscomments
ON sys.sysobjects.id = sys.syscomments.id
WHERE sys.syscomments.text LIKE '%KEYWORD%'
AND sys.sysobjects.type = 'P'
ORDER BY sys.sysobjects.NAME

The INFORMATION_SCHEMA.ROUTINES view can be used to search for content of functions as well.  Simply alter your WHERE clause as follows, substituting your search string in place of "KEYWORD" once again:

SELECT ROUTINE_NAME, ROUTINE_DEFINITION
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_DEFINITION LIKE '%KEYWORD%'
AND ROUTINE_TYPE='FUNCTION'
ORDER BY ROUTINE_NAME

To highlight the fact that this process is backward-compatible to Microsoft SQL Server 2000, I've run the following query against the Northwind database.  I'm interested in finding all the stored procedures that utilize the ROUND() function:

USE Northwind
GO
SELECT ROUTINE_NAME, ROUTINE_DEFINITION
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_DEFINITION LIKE '%ROUND%'
AND ROUTINE_TYPE='PROCEDURE'
ORDER BY ROUTINE_NAME
GO


added 4 years 4 months ago

- Generate object from xml in c#
- 24 Useful Distinct Color Codes
- How to draw an arrow with DrawLine command
- How to write Snake game in C#?
- Break Parallel Foreach Earlier
- How to find all Stored Procedures having a given text inside
- How to Create and Write Excel File in C#
- Extension Methods
- Microsoft Code Contracts
- VersaFix
- QuickFIX
- fix4net
- Converting String to Double in C#
- C# File Operations, C# Input Output (C# I/O)
2
1