SQL statement used to view deadlocks

1. If exists (Select*FromDbo. sysobjectsWhereId = object_id (N'[Dbo]. [sp_who_lock]')AndOBJECTPROPERTY (id, N'Isprocedure') = 1)
2. Drop Procedure[Dbo]. [sp_who_lock]
3. GO
4. /*************************************** ************************************
5. // Create:
6. // Date:
7. // Modify:
8. // Description: view the blocking and deadlock conditions in the database
9. **************************************** ***********************************/
10. Use master
11. Go
12. Create ProcedureSp_who_lock
13. As
14. Begin
15. Declare@ SpidInt, @ BlInt,
16. @ IntTransactionCountOnEntryInt,
17. @ IntRowcountInt,
18. @ IntCountPropertiesInt,
19. @ IntCounterInt
21. Create Table# Tmp_lock_who (
22. IdIntIdentity (1, 1 ),
23. SpidSmallint,
24. BlSmallint)
26. IF @ ERROR <> 0RETURN@ ERROR
28. Insert Into# Tmp_lock_who (spid, bl)Select0, blocked
29. From(Select*FromSysprocessesWhereBlocked> 0)
30. Where NotExists (Select*From(Select*FromSysprocessesWhereBlocked> 0) B
31. WhereA. blocked = spid)
32. Union SelectSpid, blockedFromSysprocessesWhereBlocked> 0
34. IF @ ERROR <> 0RETURN@ ERROR
36. -- Find the number of records in the temporary table
37. Select@ IntCountProperties =Count(*), @ IntCounter = 1
38. From# Tmp_lock_who
40. IF @ ERROR <> 0RETURN@ ERROR
42. If @ intCountProperties = 0
43. Select 'No blocking and deadlock information' AsMessage
45. -- Start of Loop
46. While @ intCounter <= @ intCountProperties
47. Begin
48. -- Retrieve the first record
49. Select@ Spid = spid, @ bl = bl
50. From# Tmp_lock_whoWhereId = @ intCounter
51. Begin
52. If @ spid = 0
53. Select 'The cause of database deadlock is :'+CAST(@ BlAS VARCHAR(10) +'Process number, and the SQL syntax executed by it is as follows'
54. Else
55. Select 'Process ID :'+CAST(@ SpidAS VARCHAR(10) +'Be'+'Process ID :'+CAST(@ BlAS VARCHAR(10) +'Blocking, the SQL syntax executed by the current process is as follows'
56. Dbcc inputbuffer (@ bl)
57. End
59. -- Move the loop pointer down
60. Set@ IntCounter = @ intCounter + 1
61. End
64. Drop Table# Tmp_lock_who
66. Return0
67. End