Rahul Hera Rahul Hera - 6 days ago 5
SQL Question

How to find list of tables used in stored procedure without "With (nolock)" words

I have large table data and each table need to be end with the statement (nolock) at the end , please help me to find in all stored procedure.

Example:

if a store-procedure used two tables a and b and one table b doesn't end with (NOLOCK) then i need to return following details.

SP_Name,Table_name

Answer

If I understand you correctly, you are looking for all the store procedure names that have the nolock keyword:

SELECT ROUTINE_NAME, ROUTINE_DEFINITION
    FROM INFORMATION_SCHEMA.ROUTINES 
    WHERE ROUTINE_DEFINITION LIKE '%nolock%' 
    AND ROUTINE_TYPE='PROCEDURE'
Comments