Query to list all the procedures and triggers not having SET NOCOUNT ON defined from Blog Posts – SQLServerCentral


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[1].

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

The post Query to list all the procedures and triggers not having SET NOCOUNT ON defined[2] appeared first on SQLServerCentral[3].

References

  1. ^ here (docs.microsoft.com)
  2. ^ Query to list all the procedures and triggers not having SET NOCOUNT ON defined (www.sqlservercentral.com)
  3. ^ SQLServerCentral (www.sqlservercentral.com)


Source: Programming News

January 15, 2021
Category : Marketing
Tags: Databases News | News | SQL | SQL server

Leave a Reply

Your email address will not be published. Required fields are marked *

Sitemap | Terms | Privacy | Cookies | Advertising

Senior Software Developer

Creator of @LzoMedia I am a backend software developer based in London who likes beautiful code and has an adherence to standards & love's open-source.