Query to list all the procedures and triggers not having SET NOCOUNT ON defined

It is a tiny query in size but can play a crucial role to avoid overhead, caused due to missing SET NOCOUNT ON definition in the stored procedures and triggers.

Read more about SET NOCOUNT here.

Here are few useful articles talking about why it’s important to add SET NOCOUNT ON.

• SET NOCOUNT ON Improves SQL Server Stored Procedure Performance (mssqltips.com)
• Performance Surprises and Assumptions : SET NOCOUNT ON – SQLPerformance.com

SELECT OBJ.[name] AS ObjectName
, 
CASE
WHEN OBJ.[xtype] = 'P'
THEN 'Stored Procedure' 
WHEN OBJ.[xtype] = 'TR'
THEN 'Trigger'
END AS ObjectType
FROM sys.sysobjects OBJ
WHERE OBJECT_DEFINITION(OBJ.[id]) NOT LIKE ('%SET%NOCOUNT%ON%')
AND OBJ.[xtype] IN ('P', 'TR')