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  :	http://www.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