34,590
社区成员
发帖
与我相关
我的任务
分享
sp_MSget_current_activity
dbo stored procedure
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
-- =============================================
-- sp_MSget_current_activity
-- =============================================
alter procedure dbo.sp_MSget_current_activity @id int = 0, @option int = 0, @obj nvarchar(386) = null, @spid int = 0
as
if (@id = 0)
begin
raiserror(N'No SPID specified (spid = %d)', 1, 1, @id)
return(-1)
end
if (@option <= 0 or @option > 5)
begin
raiserror(N'Invalid option %d', 1, 1, @option)
return(-1)
end
declare @stmt as nvarchar(4000)
-- =============================================
-- make tables SPID depended
-- =============================================
declare @locktab as sysname
declare @proctab as sysname
set @locktab = N'##lockinfo' + rtrim(convert(nvarchar(5), @id))
set @proctab = N'##procinfo' + rtrim(convert(nvarchar(5), @id))
if (@option = 1)
begin
-- process info (overview of all processes by SPID)
set @stmt = N'select [Process ID], [User], [Database], [Status], [Open Transactions], [Command], [Application], [Wait Time], [Wait Type], [Wait Resource], [CPU], [Physical IO], [Memory Usage], [Login Time], [Last Batch], [Host], [Net Library], [Net Address], [Blocked By], [Blocking], [Execution Context ID] from ' + @proctab + ' order by [Process ID],[Execution Context ID]'
end
else if (@option = 2)
begin
-- distinct spid list (old)
-- set @stmt = N'select [Process ID], [Blocking], [Blocked By] from ' @proctab + ' order by [Process ID]'
-- distinct spid list, only spids with locks
set @stmt = N'select distinct L.[Process ID], P.[Blocking], P.[Blocked By] from ' + @locktab + ' L, ' + @proctab + ' P where L.[Process ID] = P.[Process ID] order by L.[Process ID]'
end
else if (@option = 3)
begin
-- distinct object list
set @stmt = N'select distinct [Object] from ' + @locktab + ' order by [Object]'
end
else if (@option = 4)
begin
-- locks per spid
if (@spid = 0)
begin
raiserror(N'Error @spid parameter not specified (option %d)', 1, 1, @option)
return(-1)
end
set @stmt = N'select [Object], [Lock Type], [Mode], [Status], [Owner], [Index], [Resource] from ' + @locktab + ' where [Process ID] = ' + rtrim(convert(nvarchar(10), @spid)) + ' order by [Object]'
end
else if (@option = 5)
begin
-- locks per object
if (@obj is null)
begin
raiserror(N'Error @obj parameter not specified (option %d)', 1, 1, @option)
return(-1)
end
-- locked object is db
if parsename(@obj,3) is null
begin
set @stmt = N'select [Process ID], [Lock Type], [Mode], [Status], [Owner], [Index], [Resource] from ' + @locktab + ' where [Object] = ''' + @obj + ''' and [ObjID] = 0'
end
-- locked object is table
else
begin
set @stmt = N'select [Process ID], [Lock Type], [Mode], [Status], [Owner], [Index], [Resource] from ' + @locktab + ' where [Object] = ''' + parsename(@obj,3) + '.' + parsename(@obj,2) + '.' + parsename(@obj,1) + ''''
end
end
exec (@stmt)
return(0)
-- =============================================
-- end sp_MSget_current_activity
-- =============================================
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
--try
select hostname,login_time,last_batch,[status]
from sys.sysprocesses
where not hostname = ''
sp_MSget_current_activity 64,1