sql - How to find list of tables used in stored procedure with “nolock”? -

i have more 1000 stored procedures, due dirty programmers have used 'nolock' stored procedure tables.

now facing lots , lots of issues due issue in daily basics.

for reasons cant remove nolock key words sp need know in stored procedure occurs.

example:

let consider store-procedure(sp_user) used 2 tables (tbl_salary) , (tbl_account) , if 1 table (tbl_account) end (nolock) need return following details.

**s.n    sp_name   table_name**  1      sp_user  tbl_account 

any me appreciated...

try following simple query. give sp,functions , views in "nolock" using irrespective of manner.

select distinct o.name object_name,o.type_desc     sys.sql_modules m      inner join sys.objects o      on m.object_id=o.object_id     m.definition '%nolock%' 

and following stored procedures only

select distinct o.name object_name sys.sql_modules m  inner join sys.objects o  on m.object_id=o.object_id m.definition '%nolock%' , o.type = 'p' 

i have worked around , have solution. please try:

;with stored_procedures ( select  o.name proc_name, oo.name table_name, row_number() over(partition o.name,oo.name order o.name,oo.name) row sysdepends d  inner join sys.objects o on o.object_id=d.id inner join sys.objects oo on oo.object_id=d.depid , oo.type = 'u' inner join sys.sql_modules m on m.object_id=o.object_id inner join sys.sql_modules mod on o.object_id = mod.object_id o.type = 'p' , o.is_ms_shipped = 0  , m.definition '%nolock%') select proc_name, table_name stored_procedures row = 1 order proc_name,table_name