Meu problema: a execução sp_refreshsqlmoduleatualizará o sys.objects.modify_datevalor, mas não acionará um evento DDL que possa ser usado por um gatilho DDL. Portanto, se estiver usando um gatilho DDL para mostrar o histórico de alterações do objeto, não consigo reconciliar o motivo pelo qual um procedimento, função etc. teria um recente modify_datequando nenhuma ALTERinstrução foi executada no objeto. Isso pode causar algumas dores de cabeça durante o tempo de auditoria.
Minha pergunta: Existe alguma maneira de capturar sp_refreshsqlmoduleeventos, para que eu possa ter uma maneira automatizada de reconciliar os modify_dateobjetos do banco de dados?
Consegui encontrar este item de conexão para SQL Server 2008 https://connect.microsoft.com/SQLServer/feedback/details/340441/sp-autostats-and-other-system-procedures-does-not-fire-ddl- triggers , que é marcado como fechado com o comentário:
não achamos que seja crítico corrigir isso para o SQL Server 2008. Definitivamente, consideraremos isso para o lançamento depois disso
Também sou capaz de replicar o problema no SQL Server 2014.
Script SQL para replicar o problema:
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
Os resultados deste script mostram:
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
O evento para o sp_refreshsqlmodulenão foi encontrado em nenhum lugar e todos os eventos registrados são anteriores ao sp_refreshsqlmodulehorário de 16/10/2015 10:59:15.487 (nota: coloquei um atraso de 5 segundos antes da sp_refreshsqlmodulechamada no script acima para torná-lo mais perceptível que este evento não estava sendo registrado).