SQL Trace to detect SET NOCOUNT OFF

Question

I have inherited an application that has a 9000+ stored procedures.  In a previous upgrade, the publisher added "SET NOCOUNT ON" to many of the procedures, but apparently not all.  I am trying to determine which procedures don't have this and may need to be fixed.  Can SQL trace or profiling detect activity from a proc that is running with SET NOCOUNT OFF - ?  Or is there another approach to detect this activity?

If this is not the correct forum, please kindly redirect me.

Thanks

---

tgoddard

Answer 1

If you want to know the proc without SET NOCOUNT - you can try the below:

Select OBJECT_ID, OBJECT_NAME(object_id) From sys.objects where type= 'P' and object_id not in (
Select object_id From sys.sql_modules where definition like '%SET NOCOUNT%')

Answer 2

You don't need to trace the procedures, Have a look a sys.sql_modules

select object_name(object_id) from sys.sql_modules
where definition not like '%NOCOUNT%'

---

Satheesh
My Blog | How to ask questions in technical forum

Answer 3

Yes thanks very much for this - already knew how to detect the presence/absence of a string in the source of the procedures.  However, with such a large amount - over 9000 - I was really looking for a way to detect the actual activity as its likely that only a small subset of the procedures are actually being used.  So - can SQL activity from a procedure with SET NOCOUNT OFF be detected via a trace?

Thanks

---

tgoddard

Answer 4

So I don't think you could filter the SPs only having NOCOUNT OFF in profiler.  Probably what you could do is a combination of the trace and the above query. 

First you could use the  trace to capture all the activities of the server, and parse all the stored procedure names them using a T-SQL (use the SP_STARTING OR SP_COMPLETED events and the ObjectName  in the output columns while starting the trace) 

You can later load the trace using the sql statement

SELECT
	objectname
	FROM fn_trace_gettable('D:\TrC1.trc', 10)	
	where OBJECTNAME is not null

Now use the above query to filter out the SPs that are having NOCOUNT OFF and join it with the trace output using the object name and you can get the SPs that are getting executed in the system and have NOCOUNT OFF. 

Select OBJECT_ID, OBJECT_NAME(object_id) From sys.objects where type= 'P' and object_id not in (
Select object_id From sys.sql_modules where definition like '%SET NOCOUNT%')

Does this help you?

---

Satheesh
My Blog | How to ask questions in technical forum

Answer 5

So - can SQL activity from a procedure with SET NOCOUNT OFF be detected via a trace?

Yes, a SQL Trace or Extended Events trace with the sp statement completed event will capture all stored proc statements.  Include the object name in the trace add a filter on text LIKE '%SET%NOCOUNT%OFF%'.

---

Dan Guzman, SQL Server MVP, http://www.dbdelta.com