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.