SQLServer查看死锁

if exists (

       select *

       from   sys.procedures

       where  name like '%USP_ShowLocks%'

   )

    drop procedure dbo.USP_ShowLocks

go

create procedure dbo.USP_ShowLocks

as

begin

	create table #t

	(

		req_spid     int

	   ,obj_name     sysname

	)  

	declare @s           nvarchar(4000)

	       ,@rid         int

	       ,@dbname      sysname

	       ,@id          int

	       ,@objname     sysname  

	declare tb cursor

	for

	    select distinct req_spid

	          ,dbname = db_name(rsc_dbid)

	          ,rsc_objid

	    from   master..syslockinfo

	    where  rsc_type   in (4 ,5)

	open tb

	fetch next from tb into @rid,@dbname,@id

	while @@fetch_status = 0

	begin

	    set @s = 'select   @objname=name   from   [' + @dbname + ']..sysobjects   where   id=@id'

	    exec sp_executesql @s

	        ,N'@objname   sysname   out,@id   int'

	        ,@objname out

	        ,@id

	    insert into #t

	    values

	      (

	        @rid

	       ,@objname

	      )

	    fetch next from tb into @rid,@dbname,@id

	end

	close tb

	deallocate tb  

	select process_id = a.req_spid

	      ,databaseName = db_name(rsc_dbid)

	      ,typeName = case rsc_type

	                  	when 1 then 'NULL   Resource（Not Use）'

	                  	when 2 then 'DataBase'

	                  	when 3 then 'File'

	                  	when 4 then 'Index'

	                  	when 5 then 'Table'

	                  	when 6 then 'Page'

	                  	when 7 then 'Key'

	                  	when 8 then 'Extend Disk '

	                  	when 9 then 'RID（Row ID)'

	                  	when 10 then 'Application Program'

	                  end

	      ,objectID = rsc_objid

	      ,objectName = b.obj_name

	      ,rsc_indid

	from   master..syslockinfo a

	       left   join #t b on  a.req_spid = b.req_spid

	where  db_name(rsc_dbid) = 'siteweaver'

	drop table #t

	--show all locks

	SELECT request_session_id as spid,

	ObjectName = case when resource_type='OBJECT' then OBJECT_NAME(resource_associated_entity_id) else cast(resource_associated_entity_id as varchar(200)) end

	 ,*

	FROM sys.dm_tran_locks

	WHERE resource_type in('OBJECT')

	--WHERE resource_type in('KEY','PAGE','OBJECT')

/* 	SELECT request_session_id, resource_type, resource_associated_entity_id,

    request_status, request_mode, resource_description

    FROM sys.dm_tran_locks

	where resource_type in('KEY','PAGE','OBJECT') */

end

go

/*杀掉死锁的进程*/

--kill 75