CREATE PROC sp_lock2
(
@dbname sysname = NULL,
@spid int = NULL
)
AS
/************************************************************************************
Copyright © 2001 Narayana Vyas Kondreddi. All rights reserved.
Purpose: To display detailed lock information
Written by: Narayana Vyas Kondreddi
Tested on: SQL Server 7.0 and SQL Server 2000
Date modified: August-13-2001 12:00 AM
Examples:
To see all the locks:
EXEC sp_lock2
To see all the locks in a particular database, say 'pubs':
EXEC sp_lock2 pubs
To see all the locks held by a particular spid, say 53:
EXEC sp_lock2 @spid = 53
To see all the locks held by a particular spid (23), in a particular database (pubs):
EXEC sp_lock2 pubs, 23
***********************************************************************************/
BEGIN
SET NOCOUNT ON
CREATE TABLE #lock
(
spid int,
dbid int,
ObjId int,
IndId int,
Type char(5),
Resource char(20),
Mode char(10),
Status char(10)
)
INSERT INTO #lock EXEC sp_lock
IF @dbname IS NULL
BEGIN
IF @spid IS NULL
BEGIN
SELECT a.spid AS SPID,
(SELECT DISTINCT program_name FROM master..sysprocesses WHERE spid = a.spid) AS [Program Name],
db_name(dbid) AS [Database Name], ISNULL(object_name(ObjId),'') AS [Object Name],IndId, Type, Resource, Mode, Status
FROM #lock a
END
ELSE
BEGIN
SELECT a.spid AS SPID,
(SELECT DISTINCT program_name FROM master..sysprocesses WHERE spid = a.spid) AS [Program Name],
db_name(dbid) AS [Database Name], ISNULL(object_name(ObjId),'') AS [Object Name],IndId, Type, Resource, Mode, Status
FROM #lock a
WHERE spid = @spid
END
END
ELSE
BEGIN
IF @spid IS NULL
BEGIN
SELECT a.spid AS SPID,
(SELECT DISTINCT program_name FROM master..sysprocesses WHERE spid = a.spid) AS [Program Name],
ISNULL(object_name(a.ObjId),'') AS [Object Name],a.IndId,
ISNULL((SELECT name FROM sysindexes WHERE id = a.objid and indid = a.indid ),'') AS [Index Name],
a.Type, a.Resource, a.Mode, a.Status
FROM #lock a
WHERE dbid = db_id(@dbname)
END
ELSE
BEGIN
SELECT a.spid AS SPID,
(SELECT DISTINCT program_name FROM master..sysprocesses WHERE spid = a.spid) AS [Program Name],
ISNULL(object_name(a.ObjId),'') AS [Object Name],a.IndId,
ISNULL((SELECT name FROM sysindexes WHERE id = a.objid and indid = a.indid ),'') AS [Index Name],
a.Type, a.Resource, a.Mode, a.Status
FROM #lock a
WHERE dbid = db_id(@dbname) AND spid = @spid
END
END
DROP TABLE #lock
END
'SQL' 카테고리의 다른 글
| [MSSQL] DBCC DBCHECK (0) | 2015.08.10 |
|---|---|
| [MSSQL] 락(lock) 걸린 쿼리(Query)를 확인하고 해제하기 (0) | 2015.07.29 |
| [MSSQL]서버 및 데이터베이스 정보 확인 (0) | 2015.07.15 |
| [MSSQL] 소수점에 값있는 데이터 찾기 (ex 0.5, 1.5, 1.25) (0) | 2015.05.28 |
| [MSSQL] DB 내 INDEX 확인 (0) | 2015.05.06 |
