How to identify Process Name of a Process Id in SQL Server

We had an immediate requirement to identify process names of process ids in SQL Server. The first reaction to the requirement was to refer the program name. However, program name is customized through application, it was not the expected result.

I tried multiple ways to identify a simple solution within SQL Server, but failed to identify a straight forward solution.(You can share if you have any simpler solution.)

With the help of Powershell script, developed a small script to get the process name as below. Hope, this will be helpful for you in the similar situation.

Code:

create Table #TblProcessName(ProcessName varchar(max))

Insert into #TblProcessName
Exec xp_cmdshell 'powershell.exe "Get-Process | Select ProcessName,Id | % {$_.ProcessName + " "- + $_.Id}"' 

;With cte as
(
Select Substring(Processname,0,charindex('-',Processname)) as ProcessName, Substring(Processname,charindex('-',Processname)+1 , Len(processname)) as ProcessID 
From #TblProcessName A
)
Select * From cte A
Inner join sys.sysprocesses B on A.ProcessID = B.hostprocess

DROP Table #TblProcessName

Output:

Advertisements