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
BEGIN
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
ELSE
IF @i = 2--Pid
BEGIN
IF PATINDEX('%.exe%', @s) = 0
SET @s = STUFF(@s, 1, PATINDEX('%[0-9]%', @s) - 1, '')
ELSE
SET @s = LTRIM(SUBSTRING(@s,
PATINDEX('%.exe%', @s) + 4,
LEN(@s)))
SELECT @s = RTRIM(LEFT(@s, PATINDEX('%[^0-9]%', @s) - 1))
END
ELSE
IF @i = 3--Session_name
BEGIN
IF PATINDEX('%.exe%', @s) = 0
SET @s = STUFF(@s, 1, PATINDEX('%[0-9]%', @s) - 1,
'')
ELSE
SET @s = LTRIM(SUBSTRING(@s,
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))
END
ELSE
IF @i = 4--Session#
BEGIN
IF PATINDEX('%.exe%', @s) = 0
SET @s = STUFF(@s, 1,
PATINDEX('%[0-9]%', @s) - 1, '')
ELSE
SET @s = LTRIM(SUBSTRING(@s,
PATINDEX('%.exe%', @s)
+ 4, LEN(@s)))
SELECT @s = STUFF(@s, 1,
PATINDEX('%[^0-9]%', @s) - 1,
'')
,@s = STUFF(@s, 1,
PATINDEX('%[0-9]%', @s) - 1, '')
,@s = RTRIM(LEFT(@s,
PATINDEX('% %', @s) - 1))
END
ELSE
IF @i = 5--memory
BEGIN
IF PATINDEX('%.exe%', @s) = 0
SET @s = STUFF(@s, 1,
PATINDEX('%[0-9]%', @s) - 1,
'')
ELSE
SET @s = LTRIM(SUBSTRING(@s,
PATINDEX('%.exe%',
@s) + 4, LEN(@s)))
SELECT @s = STUFF(@s, 1,
PATINDEX('%[0-9]%', @s) - 1,
'')
,@s = STUFF(@s, 1,
PATINDEX('%[^0-9]%', @s)
- 1, '')
,@s = STUFF(@s, 1,
PATINDEX('%[0-9]%', @s) - 1,
'')
,@s = STUFF(@s, 1,
PATINDEX('%[1-9]%', @s) - 1,
'')
,@s = RTRIM(LEFT(@s,
PATINDEX('%k%', @s)
- 1))
END
ELSE
IF @i = 6--user_name
SELECT @s = STUFF(@s, 1,
PATINDEX('%[0-9]%', @s) - 1,
'')
,@s = STUFF(@s, 1,
PATINDEX('%[^0-9]%', @s)
- 1, '')
,@s = STUFF(@s, 1,
PATINDEX('%[0-9]%', @s) - 1,
'')
,@s = STUFF(@s, 1,
PATINDEX('%[1-9]%', @s) - 1,
'')
,@s = RTRIM(SUBSTRING(@s,
PATINDEX('%\%',
@s) + 1, 18))
ELSE
IF @i = 7--cup_time
SELECT @s = SUBSTRING(@s,
PATINDEX('%[0-9]:[0-9][0-9]:[0-9][0-9]%',
@s), 8)
ELSE
SELECT @s = RIGHT(@s,
CHARINDEX(' ',
REVERSE(@s), 3)
- 1)
RETURN @s
END
go
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
