DROP PROCEDURE sp_lock1
go
CREATE PROCEDURE sp_lock1
(
@spids varchar(30) = null
)
as
/********************************************************************************/
/*      Created By :    Umachandar Jayachandran (UC)                            */
/*      Created On :    07 Jan 1998                                             */
/*      Description:    This SP provides locking information with table names.  */
/*                      The locks for specific SPIds can also be viewed.        */
/********************************************************************************/
/*      Resources  :    https://umachandar.com/resources.htm                 */
/********************************************************************************/
declare @dbname varchar(30), @cmdstr varchar(255)
select @cmdstr = ' and l.spid in (' + @spids + ')' where @spids is not null
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
declare dbs cursor for 
select name from master..sysdatabases 
where exists(select spid from syslocks where db_name(dbid) = name)
open dbs
while('FETCH IS OK' = 'FETCH IS OK')
begin
        fetch next from dbs into @dbname
        if(@@fetch_status <> 0)
                break
        execute('
        select l.spid, table_name = o.name, l.page, locktype = v.name, 
                dbname = db_name(l.dbid) from master..syslocks l, master..spt_values v,'
        + @dbname + '..sysobjects o
                where l.type = v.number
                        and v.type = ''L''
                        and l.id = o.id
                        and o.id > 0
                        and db_name(l.dbid) = ''' + @dbname + ''''
        + @cmdstr + ' 
        union all
        select l.spid, table_name = ''-'', l.page, locktype = v.name,
                dbname = db_name(l.dbid) from master..syslocks l, master..spt_values v
                where l.type = v.number
                        and v.type = ''L''
                        and l.id = 0
                        and db_name(l.dbid) = ''' + @dbname + ''''
        + @cmdstr + ' order by 1, 2 ')
end
close dbs
deallocate dbs
go
GRANT EXECUTE ON sp_lock1 TO public
go

This page was last updated on May 01, 2006 04:28 PM.