APP_NAME() function in SQL Server returns the application name of the current session in which it is called.
When you execute the following code
SELECT APP_NAME() AS application_name
You get the following result
Microsoft SQL Server Management Studio – Query
This function can be effectively used to audit where the data come from. If you call this function inside a .NET application say VB.NET, it may return VB.NET (not sure the exact name but it will be the application name)
If you want to do some operation based on where it is called, you can make use of this function. If you want to audit your data points about the source, you can create a table with a column that has default value of APP_NAME() which will automatically stores the application name in which data come from.
Reference: Pinal Dave (https://blog.sqlauthority.com)
afaik there is a requirement for this method which is setting up application name on connection string
Can we retrive same info from ssms…..to know all the current sessions of all databases where its particullary executing