0

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.

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

Any help will me much appreciated...

Deepshikha
  • 9,896
  • 2
  • 21
  • 21
Ragul
  • 496
  • 6
  • 20

2 Answers2

1

The below query work as your need you can use directly in your sql server

;WITH stored_procedures AS ( 
    SELECT o.name AS proc_name, oo.name AS table_name, ROW_NUMBER() OVER (partition by o.name,oo.name ORDER BY o.name,oo.name) AS row 
    FROM sysdepends d INNER JOIN sysobjects o ON o.id=d.id INNER JOIN sysobjects oo ON oo.id=d.depid WHERE o.xtype = 'P'
) 
SELECT proc_name, table_name 
FROM stored_procedures 
WHERE row = 1 and proc_name in (
        select specific_name 
        from information_schema.routines 
        where routine_definition like  N'%nolock%'
    ) 
ORDER BY proc_name,table_name
Weihui Guo
  • 3,669
  • 5
  • 34
  • 56
Ashokreddy
  • 352
  • 1
  • 11
0

Try the following simple query. This will give all SP,functions and views in which "NoLock" is using irrespective of which manner. 

SELECT DISTINCT o.name AS Object_Name,o.type_desc
    FROM sys.sql_modules m 
    INNER JOIN sys.objects o 
    ON m.object_id=o.object_id
    WHERE m.definition Like '%nolock%'

And following for stored procedures only

SELECT DISTINCT o.name AS Object_Name
FROM sys.sql_modules m 
INNER JOIN sys.objects o 
ON m.object_id=o.object_id
WHERE m.definition Like '%nolock%' and o.type = 'P'

I have worked around it and have a solution. Please try:

;WITH stored_procedures AS (
SELECT 
o.name AS proc_name, oo.name AS table_name,
ROW_NUMBER() OVER(partition by o.name,oo.name ORDER BY o.name,oo.name) AS row
FROM sysdepends d 
INNER JOIN sys.objects o ON o.object_id=d.id
INNER JOIN sys.objects oo ON oo.object_id=d.depid AND 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
WHERE o.type = 'P' AND o.Is_MS_Shipped = 0 
AND m.definition Like '%nolock%')
SELECT proc_name, table_name FROM stored_procedures
WHERE row = 1
ORDER BY proc_name,table_name
Dev D
  • 225
  • 1
  • 13
  • And if you want to search only Stored Procedures then, use below query: SELECT DISTINCT o.name AS Object_Name FROM sys.sql_modules m INNER JOIN sys.objects o ON m.object_id=o.object_id WHERE m.definition Like '%nolock%' and o.type = 'P' – Dev D Jun 15 '15 at 11:01
  • If you edit your answer to include that last comment's information, it will look much better! – sstan Jun 15 '15 at 11:06
  • @Ragul - did you find your answer? – Dev D Jun 15 '15 at 11:55
  • @Dev your answer will not help me to get list of table name, It will show only list of Sp in which 'nolock' keyword used – Ragul Jun 16 '15 at 09:02
  • Ok. Let me try this. I will update my answer if I got the solution. – Dev D Jun 16 '15 at 09:58
  • @Ragul - Please see my last updated answer and let me know you are desiring the same one or not. – Dev D Jun 17 '15 at 10:31
  • Dear Dev: Please note that if a sp contains two tables even one table with nolock then all the table name will come alone with it. we need to split the definition to get the values. Even i tried lot but cant find the answers. – Ragul Jun 18 '15 at 06:11
  • Then only possible thing you have to search for nolock in SP definitions and find with which tables it is using. Its not possible by a sql query. – Dev D Jun 18 '15 at 06:14