T-SQL – Search Stored Procedure Code for Text

Often I have to search for stored procedures in SQL Server to find pieces of text.   The motivation for this is to find procedures which use a table or to look for code which the developer has done silly things like hardcoded directories (Boris….).  Luckly you can query sys.procedures to generally find the information that you need.  Below is an example of query that searches for stored procedures for the word ‘c:\*’.

REFERENCES:

Technet – sys.procedures

Summary
Article Name
Search Stored Procedure Code for Text
Author
Description
Describes how to search inside your SQL Server stored procedures for a particular piece of text. Useful for finding dependencies on other sql server object such as tables, functions, procedures.

Creative Commons License
T-SQL – Search Stored Procedure Code for Text by Carlos Ferreira, unless otherwise expressly stated, is licensed under a Creative Commons Attribution-NonCommercial-ShareAlike 3.0 Unported License.

About Carlos Ferreira

Comments

  1. Who is this Boris you speak of?

    • Boris was the best VB.Net developer alive! He doesn’t use web standards as the web will conform to him. He used to test his entire project using a single debug.assert. In HTML5 there is a secret element called which will allow data to go past the firewall…

  2. Sneaky Russians!

Leave a Reply

%d bloggers like this: