Blog Post

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.

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')

Original post (opens in new tab)
View comments in original post (opens in new tab)

Rate

Share

Share

Rate