How to capture sp_refreshsqlmodule event

My issue: executing sp_refreshsqlmodule will update the sys.objects.modify_date value, but does not fire a DDL event that can be used by a DDL trigger. So if using a DDL trigger to show object change history, I cannot reconcile the reason that a procedure, function, etc. would have a recent modify_date when no ALTER statement has been run against the object. This can cause some headaches during audit time.

My question: Is there any way to capture sp_refreshsqlmodule event, so that I can have an automated way to reconcile the modify_date for database objects?

I was able to find this connect item for SQL Server 2008 https://connect.microsoft.com/SQLServer/feedback/details/340441/sp-autostats-and-other-system-procedures-does-not-fire-ddl-triggers , which is marked as closed with the comment:


we dont think it is critical to fix this for SQL Server 2008. We will
definitely consider it for the release after that

I am able to replicate the issue in SQL Server 2014 as well.

SQL script to replicate the issue:

```
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'

Unfortunately, there are several DDL-type events that don't actually count as DDL events for usage with DDL triggers, event notifications, etc.

You can capture sp_refreshsqlmodule calls using the following Extended Events session:

CREATE EVENT SESSION [refreshes] ON SERVER 
ADD EVENT sqlserver.module_end
(
  SET collect_statement = (1)
  ACTION
  (
     sqlserver.client_app_name, 
     sqlserver.client_hostname,
     sqlserver.database_id,
     sqlserver.database_name,
     sqlserver.username,
     sqlserver.context_info
  )
  WHERE ([object_id] = -419385653) -- OBJECT_ID(N'sys.sp_refreshsqlmodule')
)
ADD TARGET package0.asynchronous_file_target
(
  SET FILENAME = N'C:\temp\refreshes.xel'
);
GO

ALTER EVENT SESSION [refreshes] ON SERVER STATE = START;
GO

You may need to collect a different set of auditing columns; these were mostly just borrowed from a similar session I have. You may also want to add an additional filter for sp_refreshview and possibly other events like recompiles and statistics updates (I do not know all of the procedure calls that might change modify_date but aren't captured as proper DDL events).

Now, the session will merely capture the data. You can inspect it manually like this:

;WITH ee_data AS 
(
  SELECT x = CONVERT(XML, event_data)
    FROM sys.fn_xe_file_target_read_file
    (N'C:\temp\refreshes*.xel', NULL, NULL, NULL)
)
SELECT 
  [statement] = x.value('(event/data[@name="statement"]/value)[1]','nvarchar(4000)'),
  [timestamp] = x.value('(event/@timestamp)[1]','datetime2'),
  --username = x.value('(event/action[@name="username"]/value)[1]','nvarchar(400)'),
  --[host] = x.value('(event/action[@name="client_hostname"]/value)[1]','nvarchar(400)'),
  --app = x.value('(event/action[@name="client_app_name"]/value)[1]','nvarchar(400)')
  -- you'll have to add the xquery stuff to get context_info
FROM ee_data;

Example output:

statement                                      timestamp
--------------------------------------------   ---------------------------
EXEC sys.sp_refreshsqlmodule N'dbo.someview'   2015-10-16 16:45:35.6220000

You'll have to parse out the object name, and put the information into your own XML format to match EVENTDATA(). Also note that timestamp is in UTC, not your local time, so you'll need to adjust that for valid comparisons to modify_date (which inherit server time). In my case the difference between modify_date and timestamp was 6 milliseconds, so the session does not record the exact moment the object was modified - you'll need to allow a little leeway to "match" these two values.

You'll then need to put whatever code you end up with into some kind of job that polls the file target for new rows (you may consider query notifications for this to avoid polling, but polling is much simpler), and inserts them into your DDL auditing table.

StackExchange Database Administrators Q#118281, answer score: 2