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