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