Querying deadlocks and processing deadlocks (SQL Server)

-------------------Query deadlock, the cause of the extreme-------------------------------
useMasterGoCreate procedureSp_who_lock asbeginDeclare @spid int,@bl int, @intTransactionCountOnEntry  int,        @intRowcount    int,        @intCountProperties   int,        @intCounter    int Create Table#tmp_lock_who (IDint Identity(1,1), spidsmallint, BLsmallint)  IF @ @ERROR<>0 RETURN @ @ERROR  Insert  into#tmp_lock_who (SPID,BL)Select  0, blocked from(Select *  fromsysprocesseswhereBlocked>0) Awhere  not exists(Select *  from(Select *  fromsysprocesseswhereBlocked>0) bwhereA.blocked=spid)Union SelectSpid,blocked fromsysprocesseswhereBlocked>0 IF @ @ERROR<>0 RETURN @ @ERROR   --number of records found for temporary table Select  @intCountProperties = Count(*),@intCounter = 1  from#tmp_lock_whoIF @ @ERROR<>0 RETURN @ @ERROR   if @intCountProperties=0  Select 'No blocking and deadlock information now'  asmessage--Cycle Start while @intCounter <= @intCountPropertiesbegin--take the first record  Select  @spid =spid@bl =BL from#tmp_lock_whowhereId= @intCounter  begin  if @spid =0             Select 'causing the database deadlock is:'+ CAST(@bl  as VARCHAR(Ten))+ 'process number, which executes the following SQL syntax' Else            Select 'process number spid:'+ CAST(@spid  as VARCHAR(Ten))+ 'was' + 'process number spid:'+ CAST(@bl  as VARCHAR(Ten))+'The SQL syntax that the current process executes is blocked as follows' DBCCInputBuffer (@bl ) End --The loop pointer moves down Set @intCounter = @intCounter + 1EndDrop Table#tmp_lock_whoreturn 0End

------Execution Statements
EXECSp_who_lock

---------Delete all indexes at once-----------
useMasterGoif exists(Select *  fromDbo.sysobjectswhereId= object_id(N'[dbo]. [P_killspid]') and ObjectProperty(ID, N'isprocedure')= 1)Drop procedure [dbo].[P_killspid]GOCreate procP_killspid@dbname varchar( $)--the database name of the process to close as      Declare @sql  nvarchar( -)      Declare @spid nvarchar( -)    Declare#tbcursor  for        Selectspid=cast(spid as varchar( -)) fromMaster.. sysprocesseswheredbid=db_id(@dbname)    Open#tbFetch Next  from#tb into @spid     while @ @fetch_status=0    begin          exec('Kill'+@spid)        Fetch Next  from#tb into @spid    End      Close#tbdeallocate#tbGo--Usage Incoming DatabaseexecP_killspid'Database name'

Querying deadlocks and processing deadlocks (SQL Server)