-5

I have more than 1000 Stored procedures, due to some dirty programmers they would have used 'nolock' for stored procedure tables.

Now i am facing lots and lots of issues due to this issue in daily basics.

For some reasons i cant remove all the nolock key words from SP but i need to know in which stored procedure it occurs along with table name.

Example:

Let us consider a store-procedure(sp_user) used two tables (tbl_salary) and (tbl_account) and if one table (tbl_account) end with (NOLOCK) then i need to return following details.

**S.N SP_Name Table_name** 1 sp_user tbl_account

I don't need Stored procedure name,i know that it can be get optained using routine_definition like clause, i need the table name only.

Any help will me much appreciated...

Ragul
  • 496
  • 6
  • 20
  • Do you check [this question](http://stackoverflow.com/q/16229493/4519059)? ;). – shA.t Jun 17 '15 at 10:04
  • @shA.t : why........? – Ragul Jun 17 '15 at 10:05
  • 2
    You have asked same question again..http://stackoverflow.com/questions/30843156/how-to-find-list-of-tables-used-in-stored-procedure-with-nolock – Deepshikha Jun 17 '15 at 10:07
  • 2
    didn't you asked this yesterday? http://stackoverflow.com/questions/30843156/how-to-find-list-of-tables-used-in-stored-procedure-with-nolock More over some days ago someone with a name similar to yours asked this and got an answer: http://stackoverflow.com/questions/30757194/how-to-find-list-of-tables-used-in-stored-procedure-without-with-nolock-word – Simone Jun 17 '15 at 10:09
  • @Deepshikha:i have modified some words so that it can be easily understandable. – Ragul Jun 17 '15 at 10:09
  • Why would using the `(NOLOCK)` hint make someone a 'dirty programmer'? There are valid reasons for doing so, for example when there is a need to perform dirty reads, or when you know that the data you are interested in is not going to change, and you want to perform a query without placing locks which might block other sessions. Make sure you are sure of your rationale for changing the existing code, or you may end up with someone else cursing **your** name in the future! – Ed B Jun 17 '15 at 10:18
  • @EdB: I agree with you thats why i mentioned "For some reasons i cant remove all the nolock key words from SP but i need to know in which stored procedure it occurs along with table name." – Ragul Jun 17 '15 at 10:20
  • I have updated my answer asked by you yesterday. Please see : http://stackoverflow.com/questions/30843156/how-to-find-list-of-tables-used-in-stored-procedure-with-nolock/30843446#30843446 – Dev D Jun 17 '15 at 10:32

3 Answers3

0

try this query

SELECT name
FROM   sys.procedures
WHERE  Object_definition(object_id) LIKE '%NOLOCK%'
Mukesh Kalgude
  • 4,814
  • 2
  • 17
  • 32
0

try this one :-

Declare  @stObjToFind       Varchar(150)

Select   @stObjToFind = 'With (Nolock)'

Select   so.id
        ,so.name
        ,so.xtype
From    sysobjects As so With (Nolock)
        Join
        (
            Select  Distinct sc.id
            From    syscomments As sc With (Nolock)
            Where   sc.text Like '%' + @stObjToFind + '%'
        ) As sc0 On so.id = sc0.id
Where   so.name <> @stObjToFind
Mihir Shah
  • 948
  • 10
  • 17
  • I need the table name along with the result which contains 'NOLOCK' Keyword – Ragul Jun 17 '15 at 10:17
  • Select name, create_date, modify_date from sys.sql_modules modu with (nolock) inner join sys.objects obj with (nolock) on modu.object_id = obj.object_id where name like '%text%' – Ragul Jun 17 '15 at 10:33
0

For Procedure and dependent TableName You can use this Query

SELECT ProcedureName,TableName FROM (
SELECT 
S1.name AS ProcedureName, S2.name AS TableName,
ROW_NUMBER() OVER(partition by S1.name,S2.name ORDER BY S1.name,S2.name) AS R 
FROM sysdepends SD
INNER JOIN sysobjects S1 ON S1.id=SD.id
INNER JOIN sysobjects S2 ON S2.id=SD.depid
WHERE S1.xtype = 'P' ) X 
where R = 1
Arun Gairola
  • 884
  • 4
  • 14