Wednesday, 30 December 2009

Find Text Inside a Stored Procedure or Used Defined Function

How to find text inside a stored procedure or user defined function

One of the most useful stored procedures I have in my toolkit that I find myself using over and over on any system that I work on is the following procedure that allows me to return a list of stored procedures and user defined functions that contain a particular string of text.

I maybe looking for all procs or UDF's that contain a table or view name or need a list of all procs that do have SET NOCOUNT ON so that I can find those that don't or I may just be looking for a particular variable name or comment within all my procs and functions.

It makes use of the system view syscomments which stores all the text within the stored procedures and user defined functions contained within your SQL Server database. This is just another example of how having knowledge of the system views is a very useful skill to know.

The code is below.

CREATE PROCEDURE [dbo].[usp_sql_find_string_in_proc_or_udf]

@FindStr AS VARCHAR(500)

AS

SET NOCOUNT ON

SELECT DISTINCT NAME AS [NAME],
CASE WHEN TYPE ='P' THEN 'PROCEDURE'
WHEN TYPE IN('FN', 'IF','TF') THEN 'FUNCTION'
END AS OBJECTTYPE
FROM SYSCOMMENTS as comm
JOIN sysobjects as obj
ON comm.id = obj.id and obj.type IN ('P','FN', 'IF', 'TF')
WHERE lower(TEXT) LIKE '%' + ltrim(rtrim(lower(@FindStr))) + '%'


You call it simply like so:

EXEC dbo.usp_sql_find_string_in_proc_or_udf 'SOME_TABLE'

EXEC dbo.usp_sql_find_string_in_proc_or_udf '@ErrorVar varchar(100)'

It's one of those simple procedures that are very handy to have and save a lot of time. Being able to find a piece of text within all your stored procedures and functions quickly could literally save you hours of hunting about on a large system.

5 comments:

HARINYA said...

Useful procedure for SQL Server Programmers.

kamona said...

Thank you for posting very useful
-----------
شركة تنظيف واجهات حجر وزجاج بمكة

Anonymous said...

I precisely wanted to appreciate you all over again. I do not know the things I would have sorted out in the absence of the entire suggestions shown by you concerning my problem. It was a difficult issue in my position, nevertheless witnessing a new expert mode you dealt with that took me to weep over gladness. I am happier for the help and as well , pray you realize what a powerful job you were putting in training the rest through your site. Most probably you haven’t met all of us.
offshore custom web development

Anonymous said...

I think other website proprietors should take this site as an model, very clean and fantastic user friendly style and design, let alone the content. You are an expert in this topic!
offshore custom web development

sriram said...



Great blog. You put Good stuff. All the topics were explained briefly. So quickly understand for me. I am waiting for your next fantastic blog. Thanks for sharing. Any course related details learn...
Php course in chennai

Latest Cheap Amazon Goods