Use master
go
IF OBJECTPROPERTY(OBJECT_ID('dbo.sp_lock_ex'), 'IsProcedure') = 1
BEGIN
    DROP PROCEDURE dbo.sp_lock_ex
    IF 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  :    https://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' ,  'Application'))
begin
        set @mesg = 'Invalid lock mode was specified. Valid values are ''Schema'', '+
                        '''Intent'', ''Insert'', ''Update'', ''Shared'', ' +
                        '''Bulk'', ''Exclusive'', ''Application'''
        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 = QUOTENAME( 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 ''Cursor''
        when 3 then ''Session'' when 4 then ''ExSession'' 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 join master..syslockreqtype lt 
inner join master..sysprocesses p 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(''*'', 60) 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 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

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