Lock 걸린 프로시저 검색 > db

[MS-SQL]쿼리분석기에서

 

[LOCK]걸린 프로시저 조회를 위해

 

[CURSOR]를 이용해

 

임시 TABLE를 만들어 조회

 

-----------------------------------------------------------

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

 

if exists (select * from tempdb.dbo.sysobjects where id = object_id('[tempdb].[dbo].[#c_Batch_Process]'))
   drop table #c_Batch_Process

 

if exists (select * from tempdb.dbo.sysobjects where id = object_id('[tempdb].[dbo].[#USER_PROCEDURE]'))
   drop table #USER_PROCEDURE

 

CREATE TABLE #c_Batch_Process (EventType [NVARCHAR](30), Parameters [INT], EventInfo [NVARCHAR](255))

 

CREATE TABLE #USER_PROCEDURE (USER_SPID [INT], USE_USER [VARCHAR](255), USER_PROGRAM [VARCHAR](255), USE_USER_NAME [VARCHAR](30), USE_PROCEDURE [VARCHAR](255))

 

DECLARE @USER_SPID             [INT]
DECLARE @USE_USER_NAME   [VARCHAR](30)
DECLARE @응용프로그램           [VARCHAR](255)
DECLARE @사용자                    [VARCHAR](255)
DECLARE @USE_PROCEDURE   [VARCHAR](255)

 

DECLARE 프로시저_CURSOR CURSOR FOR
SELECT
                  [master].[dbo].[sysprocesses].spid AS USER_SPID,
       RTRIM([master].[dbo].[sysprocesses].hostname) AS 사용자,
       RTRIM([master].[dbo].[sysprocesses].program_name) AS 응용프로그램
FROM         [master].[dbo].[sysprocesses]
WHERE      ([master].[dbo].[sysprocesses].spid > 50)
ORDER BY  [master].[dbo].[sysprocesses].spid

 

OPEN 프로시저_CURSOR

 

FETCH NEXT FROM 프로시저_CURSOR INTO @USER_SPID, @사용자, @응용프로그램

 

WHILE @@FETCH_STATUS = 0
BEGIN
      --SELECT @USER_SPID AS USER_SPID, @사용자 AS 사용자, @응용프로그램 AS 프로그램
      --DBCC INPUTBUFFER(@USER_SPID)

 

      SET @USE_USER_NAME = NULL

      SELECT @USE_USER_NAME = [interflex3].[dbo].[C_작업자].사용자

      FROM [interflex3].[dbo].[C_작업자]

      WHERE ([interflex3].[dbo].[C_작업자].ComputerName = @사용자)

 

      DELETE FROM #c_Batch_Process

 

      INSERT INTO #c_Batch_Process EXEC ('DBCC INPUTBUFFER(' + @USER_SPID + ')')

 

      SELECT @USE_PROCEDURE = EventInfo FROM #c_Batch_Process

 

      INSERT INTO #USER_PROCEDURE
                          ([USER_SPID],
                           [USE_USER],
                           [USER_PROGRAM],
                           [USE_USER_NAME],
                           [USE_PROCEDURE])
      VALUES
                          (@USER_SPID,
                           @사용자,
                           @응용프로그램,
                           @USE_USER_NAME,
                           @USE_PROCEDURE)

 

      FETCH NEXT FROM 프로시저_CURSOR INTO @USER_SPID, @사용자, @응용프로그램

END

 

CLOSE 프로시저_CURSOR

 

DEALLOCATE 프로시저_CURSOR

 

SELECT USER_SPID,
             USE_USER,
             USER_PROGRAM AS USER________________________PROGRAM,
             USE_USER_NAME,
             USE_PROCEDURE AS USE_______________________________PROCEDURE
FROM   #USER_PROCEDURE