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.
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 appeared first on SQLServerCentral.
Source: Programming News