1

I have a column in a table that I don't know who is updating it .

The table name is dbo.Events and the column name is [Status] and this column values are 1 or 2 .

I am trying to find the query that is updating rows in this column

I am familiar with the table

select * from sys.sql_modules m with(nolock);

Are there other options ?

THANKS

3

sys.sql_modules isn't going to help you - you won't know which one is causing a specific update, or who's calling it. Here's a start - create a logging table:

CREATE TABLE dbo.UpdateLog
(
  EventType  SYSNAME,
  Parameters INT,
  EventInfo  NVARCHAR(MAX),
  EventDate  DATETIME NOT NULL DEFAULT GETDATE(),
  AppName    SYSNAME  NOT NULL DEFAULT APP_NAME(),
  HostName   SYSNAME  NOT NULL DEFAULT HOST_NAME(),
  LoginName  SYSNAME  NOT NULL DEFAULT SUSER_SNAME()
);

Then create a trigger for updates to the source table:

CREATE TRIGGER dbo.tr_whatever
ON dbo.whatever
WITH EXECUTE AS OWNER
AFTER UPDATE
AS
BEGIN
  SET NOCOUNT ON;
  IF EXISTS 
  (
    SELECT 1 FROM inserted AS i
      INNER JOIN deleted AS d
      ON i.id = d.id -- use key column(s) here
      AND i.x <> d.x -- assumes column x is not nullable
  )
  BEGIN
    INSERT dbo.UpdateLog(EventType, Parameters, EventInfo)
      EXEC sys.sp_executesql N'DBCC INPUTBUFFER(@@SPID) WITH NO_INFOMSGS;'; 
  END
END
GO

Now you'll be able to see not only what query fired the trigger, but also who ran it, when, and from which application.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.