我的问题：执行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以使其成为更值得注意的是此事件未被记录）。
