sql server 查詢任務管理器資料

<a></a>

USE master

go

IF OBJECT_ID('tempdb..#temp') IS NOT NULL

DROP TABLE #temp

CREATE TABLE #temp

(

id INT IDENTITY(1, 1)

NOT NULL

,Proc_info NVARCHAR(MAX) NULL

,CONSTRAINT pk_id PRIMARY KEY ( id ASC )

)

--insert data into temp table

INSERT INTO #temp

( Proc_info )

EXEC master..xp_cmdshell 'tasklist /v'

--delete the wrong records

DELETE FROM #temp

WHERE Proc_info IS NULL

OR Proc_info LIKE '%==========%'

OR ID = 2

GO

--create a splite function

CREATE FUNCTION F_split_V2 ( @S NVARCHAR(4000), @i INT )

RETURNS NVARCHAR(100)

AS

BEGIN

IF @i = 1--Image_name

IF PATINDEX('%.exe%', @s) = 0

SET @s = RTRIM(LEFT(@s, PATINDEX('%[0-9]%', @s) - 1))

ELSE

SET @s = RTRIM(LEFT(@s, PATINDEX('%.exe%', @s) + 4))

END

IF @i = 2--Pid

SET @s = STUFF(@s, 1, PATINDEX('%[0-9]%', @s) - 1, '')

SET @s = LTRIM(SUBSTRING(@s,

PATINDEX('%.exe%', @s) + 4,

LEN(@s)))

SELECT @s = RTRIM(LEFT(@s, PATINDEX('%[^0-9]%', @s) - 1))

IF @i = 3--Session_name

SET @s = STUFF(@s, 1, PATINDEX('%[0-9]%', @s) - 1,

'')

PATINDEX('%.exe%', @s)

+ 4, LEN(@s)))

SELECT @s = STUFF(@s, 1, PATINDEX('%[^0-9]%', @s) - 1,

,@s = RTRIM(LEFT(@s,

PATINDEX('%[0-9]%', @s) - 1))

IF @i = 4--Session#

SET @s = STUFF(@s, 1,

PATINDEX('%[0-9]%', @s) - 1, '')

SELECT @s = STUFF(@s, 1,

PATINDEX('%[^0-9]%', @s) - 1,

,@s = STUFF(@s, 1,

PATINDEX('% %', @s) - 1))

IF @i = 5--memory

PATINDEX('%[0-9]%', @s) - 1,

PATINDEX('%.exe%',

@s) + 4, LEN(@s)))

PATINDEX('%[^0-9]%', @s)

- 1, '')

PATINDEX('%[1-9]%', @s) - 1,

PATINDEX('%k%', @s)

- 1))

IF @i = 6--user_name

,@s = RTRIM(SUBSTRING(@s,

PATINDEX('%\%',

@s) + 1, 18))

IF @i = 7--cup_time

SELECT @s = SUBSTRING(@s,

PATINDEX('%[0-9]:[0-9][0-9]:[0-9][0-9]%',

@s), 8)

SELECT @s = RIGHT(@s,

CHARINDEX(' ',

REVERSE(@s), 3)

- 1)

RETURN @s

SELECT dbo.F_split_V2(Proc_info, 1) AS image_name

,dbo.F_split_V2(Proc_info, 2) AS PID

,dbo.F_split_V2(Proc_info, 3) AS session_name

,dbo.F_split_V2(Proc_info, 4) AS session#

,dbo.F_split_V2(Proc_info, 5) + 'K' AS Memory

,dbo.F_split_V2(Proc_info, 6) AS User_name

,dbo.F_split_V2(Proc_info, 7) AS cpu_time

,'xp_cmdshell ''taskkill -IM ' + dbo.F_split_V2(Proc_info, 1) + ' /f''' AS script

FROM #temp