Find the right stored procedure

Let's say you work with a huge MS SQL Server database containing hundreds of user-created stored procedures. You need to find a stored procedure that performs a certain function necessary for your application, but you don't want to spend hours running sp_helptext and examining the code for 300 stored procedures. What can you do?

Here's a stored procedure I created called sp_FindProcTxt that allows you to search all the user stored procedures in your database, returning the ones whose code contains the search strin

Digg This!     StumbleUpon     Del.icio.us

RELATED CONTENT SQL Server Stored Procedures Top tips and tricks for SQL Server database development Top 10 SQL Server development tips of 2008 SQL Server trigger vs. stored procedure to receive data notification SQL Server errors, failures and other problems fixed from the trenches SQL Server and data manipulation in T-SQL How to use SQL Server 2008 hierarchyid data type SQL Server stored procedures tutorial: Write, tune and get examples Check SQL Server database and log file size with this stored procedure SQL Server source code analysis and management adds database security Configure SQL Server Service Broker for sending stored procedure data Database Development Top tips and tricks for SQL Server database development Managing the development lifecycle with Visual Studio Team System 2008 Processing XML files with SQL Server functions A first look at Visual Studio Team System 2008 Database Edition How to create a SQL inner join and outer join: Basics to get started New datetime data types in SQL Server 2008 offer flexibility Using DATEADD and DATEDIFF to calculate SQL Server datetime values SQL Server database design disasters: How it all starts SQL Server database design disasters: What not to do Secure SQL Server from SQL injection attacks

RELATED GLOSSARY TERMS Terms from Whatis.com − the technology online dictionary library  (SearchSQLServer.com) trigger  (SearchSQLServer.com)

RELATED RESOURCES 2020software.com, trial software downloads for accounting software, ERP software, CRM software and business software systems Search Bitpipe.com for the latest white papers and business webcasts Whatis.com, the online computer dictionary

g you specify. There are 2 modes: 0 (the default) returns only the names of the SPs; Mode 1 returns the entire code of the SPs.

For example, if you need to find a SP that re-indexes all the tables in your database, and thus you want to list all SPs (along with their code) that contain the word "index," you would type:

EXEC sp_FindProcTxt 'index', 1

Below is the code (note: the sysname datatype works only with SQL 2000; for SQL 7 you have to use nvarchar(128)):

For More Information