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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s