/*
	Author	:	Brahmanand Shukla (SQLServerCarpenter.com)
	Date	:	27-May-2022
	Purpose	:	To get all the objects performing committed reads. 
				This proceduere will fetch all the objects (Procedures, Functions, Triggers, View etc.) 
				that doesn't uses the NOLOCK and SET ISOLATION LEVEL READ UNCOMMITTED.
				This procedure may be helpful in performance tuning assignments and to resolve / minimize the frequent blockage and deadlock issues.

	Example: 
	EXEC usp_SQLServerCarpenter_Tools_Get_Objects_Performing_Committed_Read
*/
CREATE OR ALTER PROCEDURE usp_SQLServerCarpenter_Tools_Get_Objects_Performing_Committed_Read
AS
	BEGIN
		SET NOCOUNT ON;

		; WITH cte_objects
		AS
		(
			SELECT 'Stored Procedure' AS ObjectType
 				, OBJECT_NAME(OBJECT_ID) AS ObjectName
				, PATINDEX('%NOLOCK%', OBJECT_DEFINITION(OBJECT_ID)) AS NoLock_Pat_Index
				, PATINDEX('%ISOLATION%LEVEL%READ%UNCOMMITTED%', OBJECT_DEFINITION(OBJECT_ID)) AS Isolation_Pat_Index
			FROM sys.procedures
			UNION ALL
			SELECT 'View' AS ObjectType
				, OBJECT_NAME(OBJECT_ID) AS ObjectName
				, PATINDEX('%NOLOCK%', OBJECT_DEFINITION(OBJECT_ID)) AS NoLock_Pat_Index
				, PATINDEX('%ISOLATION%LEVEL%READ%UNCOMMITTED%', OBJECT_DEFINITION(OBJECT_ID)) AS Isolation_Pat_Index
			FROM sys.views
			UNION ALL
			SELECT 'Trigger' AS ObjectType
				, OBJECT_NAME(OBJECT_ID) AS ObjectName
				, PATINDEX('%NOLOCK%', OBJECT_DEFINITION(OBJECT_ID)) AS NoLock_Pat_Index
				, PATINDEX('%ISOLATION%LEVEL%READ%UNCOMMITTED%', OBJECT_DEFINITION(OBJECT_ID)) AS Isolation_Pat_Index
			FROM sys.triggers
			UNION ALL
			SELECT 'Function' AS ObjectType
				, OBJECT_NAME(OBJECT_ID) AS ObjectName
				, PATINDEX('%NOLOCK%', OBJECT_DEFINITION(OBJECT_ID)) AS NoLock_Pat_Index
				, PATINDEX('%ISOLATION%LEVEL%READ%UNCOMMITTED%', OBJECT_DEFINITION(OBJECT_ID)) AS Isolation_Pat_Index
			FROM sys.objects
			WHERE type in ('FN', 'IF', 'FN', 'AF', 'FS', 'FT')
		)

		SELECT ObjectType, ObjectName
		FROM cte_objects
		WHERE (NoLock_Pat_Index = 0 AND Isolation_Pat_Index = 0) 
	END