Blog Post

usp_SQLServerCarpenter_Tools_Get_Procedures_Triggers_Missing_SET_NOCOUNT_ON

,

/*
Author:Brahmanand Shukla (SQLServerCarpenter.com)
Date:27-May-2022
Purpose:To get all the stored procedures and triggers missing the use of SET NOCOUNT ON. 
This procedure may be helpful in performance tuning assignments.
Example: 
EXEC usp_SQLServerCarpenter_Tools_Get_Procedures_Triggers_Missing_SET_NOCOUNT_ON
*/CREATE OR ALTER PROCEDURE usp_SQLServerCarpenter_Tools_Get_Procedures_Triggers_Missing_SET_NOCOUNT_ON
AS
BEGIN
SET NOCOUNT ON;
; WITH cte_objects
AS
(
SELECT 'Stored Procedure' AS ObjectType
 , OBJECT_NAME(OBJECT_ID) AS ObjectName
, PATINDEX('%SET%NOCOUNT%ON%', OBJECT_DEFINITION(OBJECT_ID)) AS NoCount_Pat_Index
FROM sys.procedures
UNION ALL
SELECT 'Trigger' AS ObjectType
, OBJECT_NAME(OBJECT_ID) AS ObjectName
, PATINDEX('%SET%NOCOUNT%ON%', OBJECT_DEFINITION(OBJECT_ID)) AS NoCount_Pat_Index
FROM sys.triggers
)
SELECT ObjectType, ObjectName
FROM cte_objects
WHERE NoCount_Pat_Index <> 0 
END

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

Rate

You rated this post out of 5. Change rating

Share

Share

Rate

You rated this post out of 5. Change rating