Identifying database locks in SQL Server 2000

Try this procedure, it contains much better information that is much fit for human consumption:

Initially create the following tables in the master database and fill them with the following values:
-- Table containing the lock request mode values and text.
if object_id('syslockreqmode') is not null and
      objectproperty( object_id('syslockreqmode') , 'IsUserTable' ) = 1
      drop table syslockreqmode
go
create table syslockreqmode(req_mode int not null, req_mode_text varchar(30) not null)
insert into syslockreqmode values (0, 'NULL (No access is granted)')
insert into syslockreqmode values (1, 'Sch-S (Schema stability)')
insert into syslockreqmode values (2, 'Sch-M (Schema modification)')
insert into syslockreqmode values (3, 'IS (Intent Shared)')
insert into syslockreqmode values (4, 'SIU (Shared Intent Update)')
insert into syslockreqmode values (5, 'IS-S (Intent Shared-Shared)')
insert into syslockreqmode values (6, 'IX (Intent Exclusive)')
insert into syslockreqmode values (7, 'SIX (Shared Intent Exclusive)')
insert into syslockreqmode values (8, 'S (Shared)')
insert into syslockreqmode values (9, 'U (Update)')
insert into syslockreqmode values (10, 'IIn-Nul (Intent Insert-NULL)')
insert into syslockreqmode values (11, 'IS-X (Intent Shared-Exclusive)')
insert into syslockreqmode values (12, 'IU (Intent Update)')
insert into syslockreqmode values (13, 'IS-U (Intent Shared-Update)')
insert into syslockreqmode values (14, 'X (Exclusive)')
insert into syslockreqmode values (15, 'BU (Bulk Operation)')
go
-- Table containing the lock request type values and text.
if object_id('syslockreqtype') is not null and
      objectproperty( object_id('syslockreqtype') , 'IsUserTable' ) = 1
      drop table syslockreqtype
go

create table syslockreqtype (req_type int not null, req_type_text varchar(15) not null)
insert into syslockreqtype values(1, 'NULL Resource')
insert into syslockreqtype values(2, 'Database')
insert into syslockreqtype values(3, 'File')
insert into syslockreqtype values(4, 'Index')
insert into syslockreqtype values(5, 'Table')
insert into syslockreqtype values(6, 'Page')
insert into syslockreqtype values(7, 'Key')
insert into syslockreqtype values(8, 'Extent')
insert into syslockreqtype values(9, 'RID (Row ID)')
go

and now the procedure:

Use master
go
IF OBJECT_ID('dbo.sp_lock_ex') IS NOT NULL And
      OBJECTPROPERTY(OBJECT_ID('dbo.sp_lock_ex'), 'IsProcedure') = 1
BEGIN
    DROP PROCEDURE dbo.sp_lock_ex
    IF OBJECT_ID('dbo.sp_lock_ex') IS NOT NULL And
            OBJECTPROPERTY(OBJECT_ID('dbo.sp_lock_ex'), 'IsProcedure') = 1
        PRINT '<<< FAILED DROPPING PROCEDURE dbo.sp_lock_ex >>>'
    ELSE
        PRINT '<<< DROPPED PROCEDURE dbo.sp_lock_ex >>>'
END
go
CREATE PROCEDURE sp_lock_ex
(
@spid int = 0,
@dbname sysname = NULL,
@loginame nvarchar(256) = N'%',
@hostname nvarchar(256) = N'%',
@programname nvarchar(256) = N'%',
@ntusername nvarchar(256) = N'%',
@objectname nvarchar(256) = N'%',
@locktype nvarchar(30) = N'%',
@lockmode nvarchar(30) = N'%',
@noinddetails bit = 0
)
WITH ENCRYPTION
as
/********************************************************************************/
/* Created By      : Umachandar Jayachandran (UC)                              */
/* Created On      : 20 January 1999                                    */
/* Description      : Written to avoid using sp_lock or sp_lockinfo sps. Those sps  */
/*              are not very flexible & sp_lockinfo hangs if tempdb itself is */
/*              locked. This SP also provides more filters to use. Blocking   */
/*              info. or blocked by info. is not available here. Only locks   */
/*              apart from the executing SPID will be shown. This SP gets     */
/*              blocked too if there is a long running transaction. (NOLOCK   */
/*              hint can avoid this.)      Object names have been defaulted to 50  */
/*              characters. If you have predominantly names >50, increase the */
/*              display format accordingly. I designed the SP in such a way   */
/*              that it will eliminate the @@SPID for the current under which */
/*              you are calling. This can be modified if need be. This SP also*/
/*              avoids using  CURSORS or TEMPORARY TABLES to minimize the       */
/*              impact on other processes.                              */
/********************************************************************************/
/*      Resources  :      http://www.umachandar.com/resources.htm               */
/********************************************************************************/
set nocount on
set transaction isolation level read committed
declare @dbid varchar(10), @spid_str varchar(10), @curspid_str varchar(10),
      @curdbname nvarchar(128), @processed_dbnames nvarchar(4000), @mindbid int,
      @indnamecol varchar(255), @indtbljoin varchar(255), @mesg varchar(255)
if (@dbname <> '%') and (db_id(@dbname) is null)
begin
      raiserror ('Invalid database name was specified.', 1, 2)
      return(-1)
end
select @spid_str = str(@spid), @curspid_str = str(@@spid), @processed_dbnames = '',
      @ntusername = case when right(@ntusername, 1) = '%'
                        then (@ntusername)
                        else (@ntusername + '%')
                  end,
      @hostname = case when right(@hostname, 1) = '%'
                        then (@hostname)
                        else (@hostname + '%')
                end,
      @loginame = case when right(@loginame, 1) = '%'
                        then (@loginame)
                        else (@loginame + '%')
                end,
      @programname = case when right(@programname, 1) = '%'
                        then (@programname)
                        else (@programname + '%')
                   end,
      @objectname = case when right(@objectname, 1) = '%'
                        then (@objectname)
                        else (@objectname + '%')
                  end,
      @indnamecol = case @noinddetails when 1
                  then 'convert(varchar(50), ltrim(str(l.rsc_indid))) as "Index ID"'
                  else 'convert(varchar(50), i.name) as "Index Name"'
                  end,
      @indtbljoin = case @noinddetails when 1
                  then ''
                  else ' left outer join sysindexes i (READPAST) ' +
                        'on l.rsc_objid = i.id and i.indid = l.rsc_indid '
                  end,
      @dbname = ISNULL(@dbname, DB_NAME())
if (@lockmode <> '%') and
      (@lockmode not in ('Schema', 'Intent', 'Insert', 'Update',
                        'Shared', 'Bulk', 'Exclusive'))
begin
      set @mesg = 'Invalid lock mode was specified. Valid values are ''Schema'', '+
                  '''Intent'', ''Insert'', ''Update'', ''Shared'', ' +
                  '''Bulk'', ''Exclusive'''
      raiserror (@mesg, 1, 2)
      return(-1)
end
if (@locktype <> '%') and
      (@locktype not in ('Database', 'File', 'Table', 'Extent',
                        'Index', 'Page', 'Key', 'RID'))
begin
      set @mesg = 'Invalid lock type was specified. Valid values are ''Database'', '+
                  '''File'', ''Table'', ''Extent'', ''Index'', ' +
                  '''Page'', ''Key'', ''RID'''
       raiserror (@mesg, 1, 2)
      return(-1)
end
select @mindbid = min(dbid)
from master..sysdatabases (READPAST)
where name like @dbname and charindex(name, @processed_dbnames) = 0

while(@mindbid is not null)
begin
      select @dbid = str(@mindbid),
            @processed_dbnames = @processed_dbnames + ', ' + name,
            @curdbname = '[' + name + ']'
      from master..sysdatabases (READPAST)
      where dbid = @mindbid

exec('use ' + @curdbname +
'select Str(req_spid) as "Process ID",
rsc_text as "Resource Text",
convert(varchar(50), case rsc_objid when 0 then ''no object name''
                  else isnull(object_name(rsc_objid), ''no object name'')
                 end) as "Object Name",
' + @indnamecol + ',
lt.req_type_text as "Resource Type",
lm.req_mode_text as "Lock Request Mode",
convert(varchar(10), case req_status when 1 then ''Granted'' when 2 then ''Converting''
      when 3 then ''Waiting'' end) as "Lock Status",
convert(varchar(12), case req_ownertype when 1 then ''Transaction'' when 2 then ''Session''
      when 3 then ''Cursor'' end) as "Lock Owner",
str(req_refcnt) as "Lock Count",
convert(varchar(30), p.hostname) as "Host Name",
convert(varchar(30), p.program_name) as "Program Name",
convert(varchar(50), p.nt_username) as "NT User Name",
convert(varchar(50), p.loginame) as "SQL Login Name"
from master..syslockreqmode lm inner join master..syslockreqtype lt
inner join master..sysprocesses p inner merge join master..syslockinfo l with( nolock ) ' + @indtbljoin + '
on l.req_spid = p.spid And p.spid <> ' + @curspid_str + ' And
l.req_spid <> ' + @curspid_str + ' And
p.spid = case ' +  @spid_str + ' when 0 then p.spid else ' + @spid_str + ' end
on l.rsc_type = lt.req_type
on l.req_mode = lm.req_mode
Where p.nt_username like ''' + @ntusername + ''' and
p.program_name like ''' + @programname + ''' and
p.hostname like ''' + @hostname + ''' and
p.loginame like ''' + @loginame + ''' and
(object_name(rsc_objid) is null or object_name(rsc_objid) like ''' + @objectname + ''') and
lt.req_type_text like ''' + @locktype + ''' and
lm.req_mode_text like ''' + @lockmode + ''' and
p.dbid = ' + @dbid +'
union all
select space(10) as "Process ID", replicate(''*'', 32) as "Resource Text",
convert(varchar(50), ''LOCKS HELD IN ' + @curdbname + ' DATABASE'') as "Object Name",
convert(varchar(50), ''(THIS IS A HEADER ROW ONLY)'') as "Index Name",
replicate(''*'', 15) as "Resource Type",
replicate(''*'', 30) as "Lock Request Mode",
replicate(''*'', 10) as "Lock Status",
replicate(''*'', 12) as "Lock Owner",
replicate(''*'', 10) as "Lock Count",
replicate(''*'', 30) as "Host Name",
replicate(''*'', 30) as "Program Name",
replicate(''*'', 50) as "NT User Name",
replicate(''*'', 50) as "SQL Login Name"
order by Str(req_spid)')

      select @mindbid = MIN(dbid)
      from master..sysdatabases (READPAST)
      where name like @dbname and charindex(name, @processed_dbnames) = 0
end
go
IF OBJECT_ID('dbo.sp_lock_ex') IS NOT NULL And
      OBJECTPROPERTY(OBJECT_ID('dbo.sp_lock_ex'), 'IsProcedure') = 1
BEGIN
    GRANT EXECUTE ON dbo.sp_lock_ex To Public
    PRINT '<<< CREATED PROCEDURE dbo.sp_lock_ex >>>'
END
ELSE
    PRINT '<<< FAILED CREATING PROCEDURE dbo.sp_lock_ex >>>'
go

I've gotten a message that SQL Server could not create sp_lock_ex, and when I try to view the stored procedure, I get the message:  Encrypted object is not transferable, and script can not be generated.

Cool.  Looks like it worked fine!