usp_SQLServerCarpenter_Tools_Get_Objects_Performing_Committed_Read

/*
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 OR Isolation_Pat_Index <> 0) 
END