如何捕获 sp_refreshsqlmodule 事件

我的问题：执行sp_refreshsqlmodule将更新sys.objects.modify_date值，但不会触发可由 DDL 触发器使用的 DDL 事件。因此，如果使用 DDL 触发器来显示对象更改历史记录，我无法调和过程、函数等在没有针对对象运行语句modify_date时会有最近的原因。ALTER这可能会在审核期间引起一些麻烦。

我的问题：有没有什么方法可以捕获sp_refreshsqlmodule事件，以便我可以有一种自动的方式来协调modify_date数据库对象？

我能够找到 SQL Server 2008 的这个连接项https://connect.microsoft.com/SQLServer/feedback/details/340441/sp-autostats-and-other-system-procedures-does-not-fire-ddl- triggers，用评论标记为关闭：

我们认为为 SQL Server 2008 修复此问题并不重要。我们肯定会在之后的版本中考虑它

我也能够在 SQL Server 2014 中复制该问题。

复制问题的 SQL 脚本：

IF OBJECT_ID('dbo.test_DDL_log ') IS NOT NULL
DROP TABLE dbo.test_DDL_log; 
GO

CREATE TABLE dbo.test_DDL_log (id int not null identity primary key, DDL_EventData xml, dateCreated datetime, contextInfo varchar(128));
go

CREATE TRIGGER [TEST_ddlDatabaseTriggerLog] 
ON DATABASE 
FOR DDL_DATABASE_LEVEL_EVENTS 
AS 
--Log all DDL operations on this database to an audit table.
BEGIN
    SET NOCOUNT ON;

    INSERT INTO dbo.test_DDL_log(DDL_EventData, dateCreated, contextInfo)
    VALUES (
          EVENTDATA()
        , GETDATE()
        , REPLACE(CAST(CONTEXT_INFO() AS VARCHAR(128)), CHAR(0), '')
    );
END
GO 

--Context info used in DDL trigger to tie to build and/or Support Ticket # to this change
DECLARE @c varbinary(128);
SET @c=cast('Ticket 123 v1.2.345' as varbinary(128));
SET CONTEXT_INFO @c;
GO

--Repro Example taken from:  https://msdn.microsoft.com/en-us/library/bb326754.aspx
-- Create an alias type.
IF EXISTS (SELECT 'TEST_mytype' FROM sys.types WHERE name = 'TEST_mytype')
DROP TYPE TEST_mytype;
GO

CREATE TYPE TEST_mytype FROM nvarchar(5);
GO

IF OBJECT_ID ('dbo.TEST_to_upper', 'FN') IS NOT NULL
DROP FUNCTION dbo.TEST_to_upper;
GO

CREATE FUNCTION dbo.TEST_to_upper (@a TEST_mytype)
RETURNS TEST_mytype
WITH ENCRYPTION
AS
BEGIN
RETURN upper(@a)
END;
GO

-- Increase the length of the alias type.
EXEC sp_rename 'TEST_mytype', 'TEST_myoldtype', 'userdatatype';
GO

CREATE TYPE TEST_mytype FROM nvarchar(10);
GO

---- The function parameter still uses the old type.
-- and would Fail here because of truncation:
--SELECT dbo.TEST_to_upper('abcdefgh'); 
GO

select modify_date_BEFORE = o.modify_date
from sys.objects o 
where o.name = 'TEST_to_upper'
;
go

WAITFOR DELAY '00:00:05'; --allow some time to elapse so that modify_date change is more noticable
GO

-- Refresh the function to bind to the renamed type.
EXEC sys.sp_refreshsqlmodule 'dbo.TEST_to_upper';
go

select modify_date_AFTER = o.modify_date
from sys.objects o 
where o.name = 'TEST_to_upper'
;
go

--only 4 events show here, NOT included the call to sp_refreshsqlmodule which updated the modify_date
SELECT * from dbo.test_DDL_log;
go

--CLEANUP 
IF OBJECT_ID ('dbo.TEST_to_upper', 'FN') IS NOT NULL
DROP FUNCTION dbo.TEST_to_upper;
GO
IF EXISTS (SELECT 'TEST_myoldtype' FROM sys.types WHERE name = 'TEST_myoldtype')
DROP TYPE TEST_myoldtype;
GO
IF EXISTS (SELECT 'TEST_mytype' FROM sys.types WHERE name = 'TEST_mytype')
DROP TYPE TEST_mytype;
GO
if exists(select 1 from sys.triggers t where t.name = 'TEST_ddlDatabaseTriggerLog')
DROP TRIGGER [TEST_ddlDatabaseTriggerLog] ON DATABASE
GO

IF OBJECT_ID('dbo.test_DDL_log ') IS NOT NULL
DROP TABLE dbo.test_DDL_log; 
GO

该脚本的结果显示：

modify_date_BEFORE
-----------------------
2015-10-16 10:59:10.447

modify_date_AFTER
-----------------------
2015-10-16 10:59:15.487

id          DDL_EventData                                                                                         dateCreated             contextInfo
----------- ------------------------------------------------------------------------------------------------ -------------------------    ------------------------
1           <EVENT_INSTANCE><EventType>CREATE_TYPE</EventType><PostTime>2015-10-16T10:59:10.443</PostTime>....... 2015-10-16 10:59:10.443 Ticket 123 v1.2.345
2           <EVENT_INSTANCE><EventType>CREATE_FUNCTION</EventType><PostTime>2015-10-16T10:59:10.447</PostTime>... 2015-10-16 10:59:10.447 Ticket 123 v1.2.345
3           <EVENT_INSTANCE><EventType>RENAME</EventType><PostTime>2015-10-16T10:59:10.450</PostTime>............ 2015-10-16 10:59:10.450 Ticket 123 v1.2.345
4           <EVENT_INSTANCE><EventType>CREATE_TYPE</EventType><PostTime>2015-10-16T10:59:10.453</PostTime>....... 2015-10-16 10:59:10.453 Ticket 123 v1.2.345

的事件sp_refreshsqlmodule无处可寻，所有记录的事件都在sp_refreshsqlmodule2015-10-16 10:59:15.487 时间之前（注意：我在上面的脚本中调用之前延迟了 5 秒sp_refreshsqlmodule以使其成为更值得注意的是此事件未被记录）。