|
/*DROP PROCEDURE sp_lock2
FUNCTION : sp_lock의 내용을 좀 더 편하게 볼 수 있도록 정보제공
작성일 : 2000-11-23, 작성자 : 성지용
*/
CREATE PROCEDURE sp_lock2
@spid1 int = NULL, /* server process id to check for locks */
@spid2 int = NULL /* other process id to check for locks */
--WITH ENCRYPTION
AS
SET NOCOUNT ON
IF @spid1 IS NOT NULL
BEGIN
SELECT CONVERT (smallint, req_spid) AS spid,
rsc_dbid AS dbid,
d.[name] AS Database_Name,
rsc_objid AS ObjId,
ISNULL(o.[name], '해당 사항 없슴') AS [Object Name],
rsc_indid AS IndId,
CASE SUBSTRING (v.name, 1, 4)
WHEN 'DB ' THEN 'DATABASE'
WHEN 'KEY ' THEN 'KEY'
WHEN 'EXT ' THEN 'EXTENT'
WHEN 'TAB ' THEN 'TABLE'
WHEN 'PAG ' THEN 'PAGE'
WHEN 'RID ' THEN 'ROW IDENTIFIER'
END AS Type,
CASE SUBSTRING (v.name, 1, 4)
WHEN 'EXT ' THEN 'FIELD : PAGE = ' + SUBSTRING (rsc_text, 1, 16)
WHEN 'PAG ' THEN 'FIEL ID : PAGE' + SUBSTRING (rsc_text, 1, 16)
WHEN 'RID ' THEN 'Field : Page : RID = ' + SUBSTRING (rsc_text, 1, 16)
ELSE SUBSTRING (rsc_text, 1, 16)
END AS Resource,
CASE SUBSTRING (u.name, 1, 8)
WHEN 'Sch-S ' THEN 'Schema Stability(Sch-S)'
WHEN 'Sch-M ' THEN 'Schema Modification(Sch-M)'
WHEN 'IS ' THEN 'Intent Shared Lock(IS)'
WHEN 'SIU ' THEN 'Shared Intent Update Lock(SIU)'
WHEN 'IS-S ' THEN 'Intent Shared-Shared Lock(IS-S)'
WHEN 'IX ' THEN 'Intent Exclusive Lock(IX)'
WHEN 'SIX ' THEN 'Shared Intent Exclusive Lock(SIX)'
WHEN 'S ' THEN 'Shared Lock(S)'
WHEN 'U ' THEN 'Update Lock(U)'
WHEN 'IIn-Nul ' THEN 'Intent Insert-NULL(IIn-Null)'
WHEN 'IS-X ' THEN 'Intent Shared-Exclusive Lock(IS-X)'
WHEN 'IU ' THEN 'Intent Update Lock(IU)'
WHEN 'IS-U ' THEN 'Intent Shared-Update Lock(IS-U)'
WHEN 'X ' THEN 'Exclusive Lock(X)'
WHEN 'I ' THEN 'Intent Lock(I)'
WHEN 'Sch ' THEN 'Schema Lock(Sch)'
WHEN 'BU ' THEN 'Bulk Operations(BU)'
END AS Mode,
SUBSTRING (x.name, 1, 5) AS Status,
CASE req_ownertype
WHEN 1 THEN 'Transaction'
WHEN 2 THEN 'Session'
WHEN 3 THEN 'Cursor'
ELSE 'N/A'
END AS Owner
FROM mASter.dbo.syslockinfo,
mASter.dbo.spt_values v,
mASter.dbo.spt_values x,
mASter.dbo.spt_values u,
mASter.dbo.sysdatabASes d,
mASter.dbo.sysobjects o
WHERE mASter.dbo.syslockinfo.rsc_type = v.number
and v.type = 'LR'
and mASter.dbo.syslockinfo.req_status = x.number
and x.type = 'LS'
and mASter.dbo.syslockinfo.req_mode + 1 = u.number
and u.type = 'L'
AND rsc_dbid = d.dbid
AND rsc_objid = o.[id]
and req_spid in (@spid1, @spid2)
END
/*
** No parameters, so show all the locks.
*/
ELSE
BEGIN
SELECT CONVERT (smallint, req_spid) AS spid,
rsc_dbid AS dbid,
d.[name] AS [Database Name],
rsc_objid AS ObjId,
ISNULL(o.[name], '해당 사항 없슴') AS [Object Name],
rsc_indid AS IndId,
CASE SUBSTRING (v.name, 1, 4)
WHEN 'DB ' THEN 'DATABASE'
WHEN 'KEY ' THEN 'KEY'
WHEN 'EXT ' THEN 'EXTENT'
WHEN 'TAB ' THEN 'TABLE'
WHEN 'PAG ' THEN 'PAGE'
WHEN 'RID ' THEN 'ROW IDENTIFIER'
END AS Type,
CASE SUBSTRING (v.name, 1, 4)
WHEN 'EXT ' THEN 'FIELD : PAGE = ' + SUBSTRING (rsc_text, 1, 16)
WHEN 'PAG ' THEN 'FIEL ID : PAGE' + SUBSTRING (rsc_text, 1, 16)
WHEN 'RID ' THEN 'Field : Page : RID = ' + SUBSTRING (rsc_text, 1, 16)
ELSE SUBSTRING (rsc_text, 1, 16)
END AS Resource,
CASE SUBSTRING (u.name, 1, 8)
WHEN 'Sch-S ' THEN 'Schema Stability(Sch-S)'
WHEN 'Sch-M ' THEN 'Schema Modification(Sch-M)'
WHEN 'IS ' THEN 'Intent Shared Lock(IS)'
WHEN 'SIU ' THEN 'Shared Intent Update Lock(SIU)'
WHEN 'IS-S ' THEN 'Intent Shared-Shared Lock(IS-S)'
WHEN 'IX ' THEN 'Intent Exclusive Lock(IX)'
WHEN 'SIX ' THEN 'Shared Intent Exclusive Lock(SIX)'
WHEN 'S ' THEN 'Shared Lock(S)'
WHEN 'U ' THEN 'Update Lock(U)'
WHEN 'IIn-Nul ' THEN 'Intent Insert-NULL(IIn-Null)'
WHEN 'IS-X ' THEN 'Intent Shared-Exclusive Lock(IS-X)'
WHEN 'IU ' THEN 'Intent Update Lock(IU)'
WHEN 'IS-U ' THEN 'Intent Shared-Update Lock(IS-U)'
WHEN 'X ' THEN 'Exclusive Lock(X)'
WHEN 'I ' THEN 'Intent Lock(I)'
WHEN 'Sch ' THEN 'Schema Lock(Sch)'
WHEN 'BU ' THEN 'Bulk Operations(BU)'
END AS Mode,
SUBSTRING (x.name, 1, 5) AS Status,
CASE req_ownertype
WHEN 1 THEN 'Transaction'
WHEN 2 THEN 'Session'
WHEN 3 THEN 'Cursor'
ELSE 'N/A'
END AS Owner
FROM mASter.dbo.syslockinfo,
mASter.dbo.spt_values v,
mASter.dbo.spt_values x,
mASter.dbo.spt_values u,
mASter.dbo.sysdatabASes d,
mASter.dbo.sysobjects o
WHERE mASter.dbo.syslockinfo.rsc_type = v.number
and v.type = 'LR'
and mASter.dbo.syslockinfo.req_status = x.number
and x.type = 'LS'
and mASter.dbo.syslockinfo.req_mode + 1 = u.number
and u.type = 'L'
AND rsc_dbid = d.dbid
AND rsc_objid *= o.[id]
ORDER BY spid
END
RETURN (0) -- sp_lock
|
