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