How to get the calling or invoking application name in SQL server

Sometime there is need to know who is calling or executing a script or stored procedure in SQL server. This is query has executed by application code or by SQL job or has executed manually by using SSMS. We get the application name by below meta data function:

SELECT APP_NAME() 

Below script get the application name of current running queries in last batch:

SELECT

     PROGRAM_NAME() AS ApplicationName,

     EST.text AS SQLScript,

     P.hostname AS HostingServerName,

     P.last_batch AS ExecutionTime    

FROM sys.sysprocesses P 
CROSS APPLY sys.dm_exec_sql_text(sql_handle) EST

Script to run stored procedure only if called by a specific application:

CREATE PROCEDURE uspLodaData

AS

BEGIN

     IF APP_NAME() = 'ExactHelpApp' BEGIN

    

          --Load data script

         

     END

    

END