CREATE TRIGGER [DTR_SaveProgrammabilityChangeHistory]
ON DATABASE WITH EXECUTE AS SELF
FOR CREATE_PROCEDURE, ALTER_PROCEDURE, DROP_PROCEDURE
    ,CREATE_FUNCTION, ALTER_FUNCTION, DROP_FUNCTION
    ,CREATE_TRIGGER, ALTER_TRIGGER, DROP_TRIGGER
    ,CREATE_TABLE

/*
Типа автоматическая версионность хранимых процедур и функций. При любом DDL-действии с ними, сохраняет код действия в таблице mgmt.DevProgrammabilityVersionControl.
При необходимости можно в нее посмотреть и для конкретной процедуры или функции достать текст на какое-либо число.
*/
AS
    SET NOCOUNT ON;

    DECLARE @data XML
           ,@objectName NVARCHAR(128)
           ,@schemaName NVARCHAR(128)
           ,@commandText NVARCHAR(MAX)
           ,@objectType NVARCHAR(100)
           ,@action NVARCHAR(100)
           ;

    SET @data = EVENTDATA();
    SELECT @objectName = @data.value('(/EVENT_INSTANCE/ObjectName)[1]', 'sysname')
          ,@schemaName = @data.value('(/EVENT_INSTANCE/SchemaName)[1]', 'sysname')
          ,@commandText = @data.value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)')
          ,@objectType = @data.value('(/EVENT_INSTANCE/ObjectType)[1]', 'nvarchar(100)')
          ,@action = @data.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)')
          ;
    IF @objectType = 'TABLE'
        AND REPLACE(@objectName, '_', '') NOT LIKE '%20[0-9][0-9][0-1][0-9][0-3][0-9]%'
        AND UPPER(@objectName) NOT LIKE '%TEST%'
            RETURN;

    INSERT INTO mgmt.DevProgrammabilityVersionControl(
        ObjSchema
        ,ObjName
        ,ObjNewText
        ,ObjVersionId
        ,ObjType
        ,ObjAction
        ,LoginName
        )
    SELECT
        @schemaName
        ,@objectName
        ,@commandText
        ,ISNULL(MAX(vc.ObjVersionId), 0) + 1
        ,@objectType
        ,@action
        ,ORIGINAL_LOGIN()
    FROM
        mgmt.DevProgrammabilityVersionControl  vc
    WHERE
        vc.ObjName = @objectName
        AND vc.ObjSchema = @schemaName
    ;
GO
DISABLE TRIGGER [DTR_SaveProgrammabilityChangeHistory] ON DATABASE
GO
ENABLE TRIGGER [DTR_SaveImportantObjects] ON DATABASE
GO
ENABLE TRIGGER [DTR_SaveProgrammabilityChangeHistory] ON DATABASE
GO