How to know SP's which are Manipulating/Updating(DML) a Table?
Suppose we have a table Employee, now we want to know the list of DB objects and users which are manipulating Employee Table
-- Create Table Employee
CREATE TABLE EMPLOYEE
-- Create Log Table to store the calling sp's/DB objects and Users.
CREATE TABLE TBL_CALLINGOBJECTS
ID INT IDENTITY(1, 1),
DTIME DATETIME DEFAULT(GETDATE()),
USERNAME VARCHAR(100) DEFAULT(SUSER_SNAME()),
SPID INT DEFAULT(@@SPID),
-- Create Trigger Start on Table Employee
CREATE TRIGGER TRIGGEREMPLOYEE_INSERT
FOR INSERT -- ,UPDATE, DELETE
DECLARE @Query VARCHAR(100)
DECLARE @SpName VARCHAR(255)
CREATE TABLE #SESSIONOBJECTS
SET @Query = 'DBCC INPUTBUFFER(' + Str(@@SPID) + ')'
INSERT INTO #SESSIONOBJECTS
SELECT @SpName = EVENTINFO FROM #SESSIONOBJECTS
INSERT INTO TBL_CALLINGOBJECTS
-- Create Trigger End on Table Employee
-- Now select the data from table TBL_CALLINGOBJECTS and check calling objects in column SPName
Once we know the calling sp's /objecs we have to delete/disable this trigger as per requirement
else trigger will keep on inserting data in log table(TBL_CALLINGOBJECTS)
right now trigger is defined for Insert command, we can modify it for Delete and Update if reqiured.