Transaction Processing Mechanism in SQL -- sp_lock2

Transaction Processing Mechanism in SQL sp_lock2Version: SQLServer7.02000Createdby: Alexander chigrikwww.mssqlcity.com

Transaction Processing Mechanism in SQL sp_lock2Version: SQL Server 7.0/2000 Created by: Alexander Chigrikhttp: // www.MSSQLCity.com/-all about ms SQL (SQL Server Articles, FAQ, Scripts, tips and Test Exams ). this stored procedure can be used instead

Transaction Processing Mechanism in SQL

sp_lock2Version: SQL Server 7.0/2000Created by: Alexander Chigrikhttp://www.MSSQLCity.com/ - all about MS SQL(SQL Server Articles, FAQ, Scripts, Tips and Test Exams). This stored procedure can be used instead of sp_lock system stored procedureto return more detailed locking view (it can return user name, host name,database name, object name, index name and object owner).This is the example to use sp_lock2:EXEC sp_lock2/*Version: SQL Server 7.0/2000Created by: Alexander Chigrikhttp://www.MSSQLCity.com/ - all about MS SQL(SQL Server Articles, FAQ, Scripts, Tips and Test Exams).This stored procedure can be used instead of sp_lock stored procedureto return more detailed locking view (it can return user name, host name,database name, object name, index name and object owner).This is the example to use sp_lock2:EXEC sp_lock2*/USE MASTERGOIF OBJECT_ID('sp_lock2') IS NOT NULL DROP PROC sp_lock2GOCREATE PROCEDURE sp_lock2@spid1 int = NULL,      /* server process id to check for locks */@spid2 int = NULL       /* other process id to check for locks */asset nocount on/*** Show the locks for both parameters.*/declare @objid int,   @indid int,   @dbid int,   @string Nvarchar(255)CREATE TABLE #locktable   (   spid       smallint   ,loginname nvarchar(20)   ,hostname  nvarchar(30)   ,dbid      int   ,dbname    nvarchar(20)   ,ObjOwner  nvarchar(128)   ,objId     int   ,ObjName   nvarchar(128)   ,IndId     int   ,IndName   nvarchar(128)   ,Type      nvarchar(4)   ,Resource  nvarchar(16)   ,Mode      nvarchar(8)   ,Status    nvarchar(5)   )if @spid1 is not NULLbegin   INSERT #locktable      (      spid      ,loginname      ,hostname      ,dbid      ,dbname      ,ObjOwner      ,objId      ,ObjName      ,IndId      ,IndName      ,Type      ,Resource      ,Mode      ,Status      )   select convert (smallint, l.req_spid)      ,coalesce(substring (s.loginame, 1, 20),'')      ,coalesce(substring (s.hostname, 1, 30),'')      ,l.rsc_dbid      ,substring (db_name(l.rsc_dbid), 1, 20)      ,''      ,l.rsc_objid      ,''      ,l.rsc_indid      ,''      ,substring (v.name, 1, 4)      ,substring (l.rsc_text, 1, 16)      ,substring (u.name, 1, 8)      ,substring (x.name, 1, 5)   from master.dbo.syslockinfo l,      master.dbo.spt_values v,      master.dbo.spt_values x,      master.dbo.spt_values u,      master.dbo.sysprocesses s   where l.rsc_type = v.number   and   v.type = 'LR'   and   l.req_status = x.number   and   x.type = 'LS'   and   l.req_mode + 1 = u.number   and   u.type = 'L'   and   req_spid in (@spid1, @spid2)   and   req_spid = s.spidend/*** No parameters, so show all the locks.*/elsebegin   INSERT #locktable      (      spid      ,loginname      ,hostname      ,dbid      ,dbname      ,ObjOwner      ,objId      ,ObjName      ,IndId      ,IndName      ,Type      ,Resource      ,Mode      ,Status      )   select convert (smallint, l.req_spid)      ,coalesce(substring (s.loginame, 1, 20),'')      ,coalesce(substring (s.hostname, 1, 30),'')      ,l.rsc_dbid      ,substring (db_name(l.rsc_dbid), 1, 20)      ,''      ,l.rsc_objid      ,''      ,l.rsc_indid      ,''      ,substring (v.name, 1, 4)      ,substring (l.rsc_text, 1, 16)      ,substring (u.name, 1, 8)      ,substring (x.name, 1, 5)   from master.dbo.syslockinfo l,      master.dbo.spt_values v,      master.dbo.spt_values x,      master.dbo.spt_values u,      master.dbo.sysprocesses s   where l.rsc_type = v.number   and   v.type = 'LR'   and   l.req_status = x.number   and   x.type = 'LS'   and   l.req_mode + 1 = u.number   and   u.type = 'L'   and   req_spid = s.spid   order by spIDENDDECLARE lock_cursor CURSORFOR SELECT dbid, ObjId, IndId FROM #locktable  WHERE Type <>'DB' and Type <> 'FIL'OPEN lock_cursorFETCH NEXT FROM lock_cursor INTO @dbid, @ObjId, @IndIdWHILE @@FETCH_STATUS = 0   BEGIN   SELECT @string =      'USE ' + db_name(@dbid) + char(13)      + 'update #locktable set ObjName = name, ObjOwner = USER_NAME(uid)'      + ' from sysobjects where id = ' + convert(varchar(32),@objid)      + ' and ObjId = ' + convert(varchar(32),@objid)      + ' and dbid = ' + convert(varchar(32),@dbId)   EXECUTE (@string)   SELECT @string =      'USE ' + db_name(@dbid) + char(13)      + 'update #locktable set IndName = i.name from sysindexes i '      + ' where i.id = ' + convert(varchar(32),@objid)      + ' and i.indid = ' + convert(varchar(32),@indid)      + ' and ObjId = ' + convert(varchar(32),@objid)      + ' and dbid = ' + convert(varchar(32),@dbId)      + ' and #locktable.indid = ' + convert(varchar(32),@indid)   EXECUTE (@string)   FETCH NEXT FROM lock_cursor INTO @dbid, @ObjId, @IndId   ENDCLOSE lock_cursorDEALLOCATE lock_cursorSELECT * FROM #locktablereturn (0)-- END sp_lock2GO