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.

System Views, Stored Procedures and SET NOCOUNT

Problems with data providers and SET NOCOUNT

Its standard good practise to always use SET NOCOUNT ON in your stored procedures to prevent system messages about the number of rows affected by DML statements from being returned. However as well as being good practise I have found that it can actually cause issues in certain cases depending on the data access provider used to connect to your SQL database from your front end application. If you have read my article about migrating from 32 bit to 64 bit applications you will see that I mention certain issues there. I have experienced problems when moving from MDAC to SQLOLEDB when stored procedures contain multiple DML statements and SET NOCOUNT has not been set as it seems these system messages are being returned as a recordset and whereas MDAC will ignore them SQLOLEDB doesn't. Therefore you may experience errors such as "Operation not allowed when the object is closed" or "item or ordinal does not exist in collection" because these system messages are being returned or accessed before your intended recordset is.


Solution - Add SET NOCOUNT ON

Therefore the solution is simple, make sure all stored procedures have this command set at the top of them. However if you have a large database containing hundreds of stored procedures then this would be quite a task to carry out manually. Therefore I came up with a way to automate this task when upgrading large database systems by using the system views available in SQL Server.

The idea is pretty simple.

• Find out which stored procedures do not contain the SET NOCOUNT statement.
• Script out those stored procedures and update them to contain the statement.
• Run the script to ALTER the stored procs and update the database
• Use standard functions and pure TSQL to accomplish the task.

Syscomments and Sysobjects

In SQL 2000 and 2005 all stored procedure and user defined functions have their source code available for viewing from the syscomments system view. The system views cannot be updated directly so its a case of using them to generate ALTER statements to run if anything requires changing.

You can view all the stored procedures without the SET NOCOUNT statement with the following SQL.

SELECT c.*,o.*
FROM sys.syscomments as c
JOIN sys.sysobjects as o
ON o.id = c.id
WHERE xtype='P' --stored procs
AND Name LIKE 'usp_%' --my prefix
AND LOWER(text) NOT LIKE '%set nocount on%'
ORDER BY o.Name

One of the things you should be aware of is that the text column which contains the code is nvarchar(4000) and procedures or functions that have a code length greater than 4000 will be split up over multiple rows. The id column relates to the system object_id which can be used to join to sysobjects and the colid will contain the subsection of code. Therefore you will notice that the previous SQL is not 100% correct as a procedure that is split over 4 rows maybe returned even if it does have SET NOCOUNT ON set because only the first section with a colid of 1 will not match the filter.


Solution to the problem

The final script I used to solve this solution can be accessed here:

Download SQL script to insert missing SET NOCOUNT ON statements to stored procedures

I have split it into two separate loops mainly to get round limitations of displaying large strings from variables in query analyser and the system stored procedure I have used to return the complete code from syscomments which is called sp_helpText.

There are multiple ways that this task could have been achieved but I wanted to keep it a purely TSQL solution and although its not the most elegant piece of code ever writen it has done the job it was designed to do which was to update 100+ stored procedures so that I didn't have to do it by hand.

To view more solutions to common database problems that I have solved by using the system views please see

http://blog.strictly-software.com/2009/05/system-tables-sysprocesses.html

http://blog.strictly-software.com/2009/02/sql-system-views.html

http://blog.strictly-software.com/2009/03/sql-server-2005-script-fulltext-indexes.html

http://blog.strictly-software.com/2008/09/script-find-text-in-database.html