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
			WHEN OBJ.[xtype] = 'P'
				THEN 'Stored Procedure' 
			WHEN OBJ.[xtype] = 'TR'
				THEN 'Trigger'
		END AS ObjectType
FROM sys.sysobjects OBJ
	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 )

Facebook photo

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

Connecting to %s