Sql-server – track down which application / procedure / function modified the value of a column in a specific table

sql-server-2008

in our enterprise application, value in one column of a table is being modified at client side. This happen usually once in a week. i have verified in all stored procedures and other modules, value in that column is just inserted first time (when some request is generated through web app) but never updated again. so it seems quite strange. now i have created a temp table and a trigger to track the record when that column is updated, but my problem is i don't know how to track the stored Procedure or any other part of db / or application which is modifying that value.

I have used HOST_NAME(), APP_NAME(), SUSER_NAME() in my trigger. these functions just return the machine IP, and db name "sa", and ".Net SQL client" things, but i need to know the actual SP / Trigger / function name which modify the value.
i cant use CONTEXT_INFO() since i don't know the actual issue point.

Any help from you experts, will be highly appreciated.

Best Answer

This might get you going along the right track. You pass the sql_handle of the existing connection to the table valued function sys.dm_exec_sql_text, and it returns the SQL text being executed. If you run this example you will see your own code, as @@SPID is your own process ID.

Source Article

SELECT t.text
FROM sys.dm_exec_requests AS r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS t
WHERE session_id = @@spid

Other system resources that may help you on your quest:

select * from sys.dm_exec_connections where session_id = @@spid
select * from sys.dm_exec_sessions where session_id = @@spid
select * from sys.dm_exec_requests where session_id = @@spid
Related Question