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
