如何找到執行事件的儲存過程？

我有一個問題，某個儲存過程偶爾會消失，我需要找出哪個腳本將其刪除。我找到了這段程式碼，它給出了與刪除這個儲存過程相關的事件。

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;

有沒有辦法可以找到哪個儲存過程或事件刪除了這個儲存過程？請指教。

對於刪除此儲存過程的任何查詢，從 DDL 觸發器獲取 SQL 只會有很大幫助。如果查詢來自儲存過程的動態 SQL，或者來自發布腳本，或者集成測試、應用程式碼等，那麼您可能只會捕捉到DROP PROCEDURE ...which 並不能提供太多關於它在哪裡的線索被處決。

但是，這並不意味著 DDL 觸發器不是解決這個問題的方法。而不是僅僅擷取 SQL 並嘗試推斷源，因為此操作是不需要的（並且可能會破壞任何呼叫正在刪除的儲存過程的程式碼），它應該被簡單地禁止。您可以使用 DDL 觸發器擷取DROP PROCEDURE事件，然後通過函式返回的 XML 檢查正在刪除的過程EVENTDATA()。如果要刪除的儲存過程是有問題的，則執行以下操作：

RAISERROR('Ah ha! Caught you red-handed (whatever that means). No DROP for you!', 16, 1);
ROLLBACK;

這樣做：

• 將防止儲存過程被刪除（無論如何這是期望的結果）。
• 將辨識 DROP 查詢的來源。可能有多個來源，尤其是在嵌套過程呼叫的情況下。此方法會將錯誤冒泡，以便發起呼叫的任何人或其他人都會看到錯誤消息並可能會提醒您，因為他們的操作未成功完成。
• 不會阻止任何其他物體掉落。

以下是一個更完整的範例，包括記錄事件的能力，以防萬一，因為它至少可以深入了解哪些人或程序正在執行此操作，以及執行頻率：

CREATE TRIGGER [PreventDropGetPendingConfiguration]
ON DATABASE
FOR DROP_PROCEDURE
AS
 SET NOCOUNT ON;

 IF (EVENTDATA().value(N'(EVENT_INSTANCE/ObjectName/text())[1]', 'sysname')
       = N'usp_GetPendingConfiguration')
 BEGIN

   -- store values in variables as ROLLBACK will erase EVENTDATA()
   DECLARE @EventTime DATETIME,
           @LoginName sysname, -- lower-case for case-sensitive servers
           @UserName sysname, -- lower-case for case-sensitive servers
           @CommandText NVARCHAR(MAX),
           @SPID INT;

   DECLARE @InputBuffer TABLE
   (
     EventType NVARCHAR(30),
     [Parameters] SMALLINT,
     EventInfo NVARCHAR(4000)
   );

   SELECT @EventTime =
                EVENTDATA().value(N'(EVENT_INSTANCE/PostTime/text())[1]', 'DATETIME'),
          @LoginName =
                EVENTDATA().value(N'(EVENT_INSTANCE/LoginName/text())[1]', 'sysname'),
          @UserName =
                EVENTDATA().value(N'(EVENT_INSTANCE/UserName/text())[1]', 'sysname'),
          @CommandText =
               EVENTDATA().value(N'(EVENT_INSTANCE/TSQLCommand/CommandText/text())[1]',
               'NVARCHAR(MAX)'),
          @SPID = EVENTDATA().value(N'(EVENT_INSTANCE/SPID/text())[1]', 'INT');

   -- RollBack now else logging will also get Rolled Back ;-)
   ROLLBACK;

   IF (OBJECT_ID(N'dbo.LoggyLog') IS NULL)
   BEGIN
     CREATE TABLE dbo.LoggyLog
     (
       LoggyLogID INT NOT NULL IDENTITY(1, 1) PRIMARY KEY,
       EventTime DATETIME NOT NULL,
       LoginName sysname, -- lower-case for case-sensitive servers
       UserName sysname, -- lower-case for case-sensitive servers
       CommandText NVARCHAR(MAX) NOT NULL,
       SPID INT NOT NULL,
       EventInfo NVARCHAR(4000) NULL
   );
   END;

   DECLARE @SQL NVARCHAR(MAX);
   SET @SQL = N'DBCC INPUTBUFFER ( ' + CONVERT(NVARCHAR(10), @SPID)
              + N' ) WITH NO_INFOMSGS;';

   INSERT INTO @InputBuffer (EventType, [Parameters], EventInfo)
     EXEC(@SQL);

   INSERT INTO dbo.LoggyLog (EventTime, LoginName, UserName, CommandText,
                             SPID, EventInfo)
     SELECT  @EventTime, @LoginName, @UserName, @CommandText, @SPID, tmp.EventInfo
     FROM    @InputBuffer tmp;

   RAISERROR('Ah ha! Caught you red-handed (whatever that means **). No DROP for you!',
             16, 1);
 END;

GO

任何刪除此儲存過程的嘗試都將收到以下錯誤：

消息 50000，級別 16，狀態 1，過程 PreventDropProcedure，第 7 行

啊哈！當場抓住你（不管那是什麼意思）。沒有 DROP 給你！Msg 3609, Level 16, State 2, Line 1

事務在觸發器中結束。該批次已中止。

我使用的原因DBCC INPUTBUFFER是sys.dm_exec_sql_text返回sys.dm_exec_sql_text目前正在執行的查詢。如果sys.dm_exec_sql_text在 Trigger 本身中本地查詢，則最終得到該CREATE TRIGGER...語句。如果在動態 SQL 或子儲存過程呼叫中查詢該 DMV，那麼您將獲得那些特定的查詢，甚至不會獲得呼叫CREATE TRIGGER它們的查詢。所有這些都是無用的。

相反，DBCC INPUTBUFFER報告鏈中的第一批（不僅僅是目前查詢），並且至少可以用於跟踪導致該呼叫的任意數量的後續呼叫DROP。

---

此外，鑑於這種情況僅在某些時候發生，可能有人在發布腳本中忘記了在呼叫之前GO執行 a 的發布腳本，並且不小心將查詢作為正在創建的儲存過程的一部分（這種情況更常見於語句，因為它們通常遵循語句）。這可能是由於發布腳本中存在以下內容：CREATE PROCEDURE``DROP PROCEDURE``DROP``GRANT EXECUTE``CREATE PROCEDURE

CREATE PROCEDURE dbo.ProcName
AS
...

-- missing GO !!!!

IF (OBJECT_ID(N'dbo.ProcGettingDropped') IS NOT NULL)
BEGIN
 DROP PROCEDURE dbo.ProcGettingDropped;
END;
GO -- this GO terminates the CREATE PROCEDURE statement

您可以通過執行以下查詢在包含要刪除的儲存過程的數據庫中搜尋此事件的出現：

SELECT OBJECT_NAME([object_id]) AS [ObjectName], *
FROM   sys.sql_modules
WHERE  [definition] LIKE N'%DROP%';

---

**紅手的詞源（感謝@MartinSmith）

引用自：https://dba.stackexchange.com/questions/111924