locked
How to find the stored procedure which executed an event? RRS feed

  • Question

  • I have a problem where a certain stored procedure disappears occasionally and I need to find out which script deletes it. I found this piece of code which gives the events related to the deletion of this stored procedure.

    DECLARE @path NVARCHAR(260);
    
    SELECT 
       @path = REVERSE(SUBSTRING(REVERSE([path]), 
       CHARINDEX(CHAR(92), REVERSE([path])), 260)) + N'log.trc'
    FROM    sys.traces
    WHERE   is_default = 1;
    
    SELECT 
      LoginName,
      HostName,
      StartTime,
      ObjectName,
      TextData
    FROM sys.fn_trace_gettable(@path, DEFAULT)
    WHERE EventClass = 47    -- Object:Deleted
    AND EventSubClass = 1
    AND ObjectName like N'%usp_GetPendingConfiguration%'
    ORDER BY StartTime DESC;

    Is there a way that I can find which stored procedure or event dropped this stored procedure? Please advice.

     

    mayooran99

    Friday, August 21, 2015 11:47 AM

Answers

All replies