History of SQL Server - scrappyCoco/SQL-Note GitHub Wiki
For audit purpose we have an ability to track all DDL code, that was submitted to SQL Server. To enable this feature we should create DDL-trigger on the database level.
The tree of available DDL events:
ALTER_SERVER_CONFIGURATION
DDL_EVENTS
| DDL_DATABASE_LEVEL_EVENTS
| | ALTER_DATABASE_SCOPED_CONFIGURATION
| | DDL_ASSEMBLY_EVENTS
| | | ALTER_ASSEMBLY
| | | CREATE_ASSEMBLY
| | | DROP_ASSEMBLY
| | DDL_COLUMN_ENCRYPTION_KEY_EVENTS
| | | ALTER_COLUMN_ENCRYPTION_KEY
| | | CREATE_COLUMN_ENCRYPTION_KEY
| | | DROP_COLUMN_ENCRYPTION_KEY
| | DDL_COLUMN_MASTER_KEY_EVENTS
| | | CREATE_COLUMN_MASTER_KEY
| | | DROP_COLUMN_MASTER_KEY
| | DDL_DATABASE_SECURITY_EVENTS
| | | DDL_APPLICATION_ROLE_EVENTS
| | | | ALTER_APPLICATION_ROLE
| | | | CREATE_APPLICATION_ROLE
| | | | DROP_APPLICATION_ROLE
| | | DDL_ASYMMETRIC_KEY_EVENTS
| | | | ALTER_ASYMMETRIC_KEY
| | | | CREATE_ASYMMETRIC_KEY
| | | | DROP_ASYMMETRIC_KEY
| | | DDL_AUTHORIZATION_DATABASE_EVENTS
| | | | ALTER_AUTHORIZATION_DATABASE
| | | DDL_CERTIFICATE_EVENTS
| | | | ALTER_CERTIFICATE
| | | | CREATE_CERTIFICATE
| | | | DROP_CERTIFICATE
| | | DDL_CRYPTO_SIGNATURE_EVENTS
| | | | ADD_SIGNATURE
| | | | ADD_SIGNATURE_SCHEMA_OBJECT
| | | | DROP_SIGNATURE
| | | | DROP_SIGNATURE_SCHEMA_OBJECT
| | | DDL_DATABASE_AUDIT_EVENTS
| | | | ALTER_AUDIT
| | | | CREATE_AUDIT
| | | | DROP_AUDIT
| | | DDL_DATABASE_AUDIT_SPECIFICATION_EVENTS
| | | | ALTER_DATABASE_AUDIT_SPECIFICATION
| | | | CREATE_DATABASE_AUDIT_SPECIFICATION
| | | | DROP_DATABASE_AUDIT_SPECIFICATION
| | | DDL_DATABASE_ENCRYPTION_KEY_EVENTS
| | | | ALTER_DATABASE_ENCRYPTION_KEY
| | | | CREATE_DATABASE_ENCRYPTION_KEY
| | | | DROP_DATABASE_ENCRYPTION_KEY
| | | DDL_GDR_DATABASE_EVENTS
| | | | DENY_DATABASE
| | | | GRANT_DATABASE
| | | | REVOKE_DATABASE
| | | DDL_MASTER_KEY_EVENTS
| | | | ALTER_MASTER_KEY
| | | | CREATE_MASTER_KEY
| | | | DROP_MASTER_KEY
| | | DDL_ROLE_EVENTS
| | | | ADD_ROLE_MEMBER
| | | | ALTER_ROLE
| | | | CREATE_ROLE
| | | | DROP_ROLE
| | | | DROP_ROLE_MEMBER
| | | DDL_SCHEMA_EVENTS
| | | | ALTER_SCHEMA
| | | | CREATE_SCHEMA
| | | | DROP_SCHEMA
| | | DDL_SYMMETRIC_KEY_EVENTS
| | | | ALTER_SYMMETRIC_KEY
| | | | CREATE_SYMMETRIC_KEY
| | | | DROP_SYMMETRIC_KEY
| | | DDL_USER_EVENTS
| | | | ALTER_USER
| | | | CREATE_USER
| | | | DROP_USER
| | DDL_DEFAULT_EVENTS
| | | BIND_DEFAULT
| | | CREATE_DEFAULT
| | | DROP_DEFAULT
| | | UNBIND_DEFAULT
| | DDL_EVENT_NOTIFICATION_EVENTS
| | | CREATE_EVENT_NOTIFICATION
| | | DROP_EVENT_NOTIFICATION
| | DDL_EXTENDED_PROPERTY_EVENTS
| | | ALTER_EXTENDED_PROPERTY
| | | CREATE_EXTENDED_PROPERTY
| | | DROP_EXTENDED_PROPERTY
| | DDL_FULLTEXT_CATALOG_EVENTS
| | | ALTER_FULLTEXT_CATALOG
| | | CREATE_FULLTEXT_CATALOG
| | | DROP_FULLTEXT_CATALOG
| | DDL_FULLTEXT_STOPLIST_EVENTS
| | | ALTER_FULLTEXT_STOPLIST
| | | CREATE_FULLTEXT_STOPLIST
| | | DROP_FULLTEXT_STOPLIST
| | DDL_FUNCTION_EVENTS
| | | ALTER_FUNCTION
| | | CREATE_FUNCTION
| | | DROP_FUNCTION
| | DDL_PARTITION_EVENTS
| | | DDL_PARTITION_FUNCTION_EVENTS
| | | | ALTER_PARTITION_FUNCTION
| | | | CREATE_PARTITION_FUNCTION
| | | | DROP_PARTITION_FUNCTION
| | | DDL_PARTITION_SCHEME_EVENTS
| | | | ALTER_PARTITION_SCHEME
| | | | CREATE_PARTITION_SCHEME
| | | | DROP_PARTITION_SCHEME
| | DDL_PLAN_GUIDE_EVENTS
| | | ALTER_PLAN_GUIDE
| | | CREATE_PLAN_GUIDE
| | | DROP_PLAN_GUIDE
| | DDL_PROCEDURE_EVENTS
| | | ALTER_PROCEDURE
| | | CREATE_PROCEDURE
| | | DROP_PROCEDURE
| | DDL_RULE_EVENTS
| | | BIND_RULE
| | | CREATE_RULE
| | | DROP_RULE
| | | UNBIND_RULE
| | DDL_SEARCH_PROPERTY_LIST_EVENTS
| | | ALTER_SEARCH_PROPERTY_LIST
| | | CREATE_SEARCH_PROPERTY_LIST
| | | DROP_SEARCH_PROPERTY_LIST
| | DDL_SECURITY_POLICY_EVENTS
| | | ALTER_SECURITY_POLICY
| | | CREATE_SECURITY_POLICY
| | | DROP_SECURITY_POLICY
| | DDL_SEQUENCE_EVENTS
| | | ALTER_SEQUENCE
| | | CREATE_SEQUENCE
| | | DROP_SEQUENCE
| | DDL_SSB_EVENTS
| | | DDL_BROKER_PRIORITY_EVENTS
| | | | ALTER_BROKER_PRIORITY
| | | | CREATE_BROKER_PRIORITY
| | | | DROP_BROKER_PRIORITY
| | | DDL_CONTRACT_EVENTS
| | | | CREATE_CONTRACT
| | | | DROP_CONTRACT
| | | DDL_MESSAGE_TYPE_EVENTS
| | | | ALTER_MESSAGE_TYPE
| | | | CREATE_MESSAGE_TYPE
| | | | DROP_MESSAGE_TYPE
| | | DDL_QUEUE_EVENTS
| | | | ALTER_QUEUE
| | | | CREATE_QUEUE
| | | | DROP_QUEUE
| | | DDL_REMOTE_SERVICE_BINDING_EVENTS
| | | | ALTER_REMOTE_SERVICE_BINDING
| | | | CREATE_REMOTE_SERVICE_BINDING
| | | | DROP_REMOTE_SERVICE_BINDING
| | | DDL_ROUTE_EVENTS
| | | | ALTER_ROUTE
| | | | CREATE_ROUTE
| | | | DROP_ROUTE
| | | DDL_SERVICE_EVENTS
| | | | ALTER_SERVICE
| | | | CREATE_SERVICE
| | | | DROP_SERVICE
| | DDL_SYNONYM_EVENTS
| | | CREATE_SYNONYM
| | | DROP_SYNONYM
| | DDL_TABLE_VIEW_EVENTS
| | | DDL_INDEX_EVENTS
| | | | ALTER_FULLTEXT_INDEX
| | | | ALTER_INDEX
| | | | CREATE_FULLTEXT_INDEX
| | | | CREATE_INDEX
| | | | CREATE_SPATIAL_INDEX
| | | | CREATE_XML_INDEX
| | | | DROP_FULLTEXT_INDEX
| | | | DROP_INDEX
| | | DDL_STATISTICS_EVENTS
| | | | CREATE_STATISTICS
| | | | DROP_STATISTICS
| | | | UPDATE_STATISTICS
| | | DDL_TABLE_EVENTS
| | | | ALTER_TABLE
| | | | CREATE_TABLE
| | | | DROP_TABLE
| | | DDL_VIEW_EVENTS
| | | | ALTER_VIEW
| | | | CREATE_VIEW
| | | | DROP_VIEW
| | DDL_TRIGGER_EVENTS
| | | ALTER_TRIGGER
| | | CREATE_TRIGGER
| | | DROP_TRIGGER
| | DDL_TYPE_EVENTS
| | | CREATE_TYPE
| | | DROP_TYPE
| | DDL_XML_SCHEMA_COLLECTION_EVENTS
| | | ALTER_XML_SCHEMA_COLLECTION
| | | CREATE_XML_SCHEMA_COLLECTION
| | | DROP_XML_SCHEMA_COLLECTION
| | RENAME
| DDL_SERVER_LEVEL_EVENTS
| | ALTER_INSTANCE
| | DDL_AVAILABILITY_GROUP_EVENTS
| | | ALTER_AVAILABILITY_GROUP
| | | CREATE_AVAILABILITY_GROUP
| | | DROP_AVAILABILITY_GROUP
| | DDL_DATABASE_EVENTS
| | | ALTER_DATABASE
| | | CREATE_DATABASE
| | | DROP_DATABASE
| | DDL_ENDPOINT_EVENTS
| | | ALTER_ENDPOINT
| | | CREATE_ENDPOINT
| | | DROP_ENDPOINT
| | DDL_EVENT_SESSION_EVENTS
| | | ALTER_EVENT_SESSION
| | | CREATE_EVENT_SESSION
| | | DROP_EVENT_SESSION
| | DDL_EXTENDED_PROCEDURE_EVENTS
| | | CREATE_EXTENDED_PROCEDURE
| | | DROP_EXTENDED_PROCEDURE
| | DDL_LINKED_SERVER_EVENTS
| | | ALTER_LINKED_SERVER
| | | CREATE_LINKED_SERVER
| | | DDL_LINKED_SERVER_LOGIN_EVENTS
| | | | CREATE_LINKED_SERVER_LOGIN
| | | | DROP_LINKED_SERVER_LOGIN
| | | DROP_LINKED_SERVER
| | DDL_MESSAGE_EVENTS
| | | ALTER_MESSAGE
| | | CREATE_MESSAGE
| | | DROP_MESSAGE
| | DDL_REMOTE_SERVER_EVENTS
| | | ALTER_REMOTE_SERVER
| | | CREATE_REMOTE_SERVER
| | | DROP_REMOTE_SERVER
| | DDL_RESOURCE_GOVERNOR_EVENTS
| | | ALTER_RESOURCE_GOVERNOR_CONFIG
| | | DDL_EXTERNAL_RESOURCE_POOL_EVENTS
| | | | ALTER_EXTERNAL_RESOURCE_POOL
| | | | CREATE_EXTERNAL_RESOURCE_POOL
| | | | DROP_EXTERNAL_RESOURCE_POOL
| | | DDL_RESOURCE_POOL
| | | | ALTER_RESOURCE_POOL
| | | | CREATE_RESOURCE_POOL
| | | | DROP_RESOURCE_POOL
| | | DDL_WORKLOAD_GROUP
| | | | ALTER_WORKLOAD_GROUP
| | | | CREATE_WORKLOAD_GROUP
| | | | DROP_WORKLOAD_GROUP
| | DDL_SERVER_SECURITY_EVENTS
| | | ADD_SERVER_ROLE_MEMBER
| | | ALTER_SERVER_ROLE
| | | CREATE_SERVER_ROLE
| | | DDL_AUTHORIZATION_SERVER_EVENTS
| | | | ALTER_AUTHORIZATION_SERVER
| | | DDL_CREDENTIAL_EVENTS
| | | | ALTER_CREDENTIAL
| | | | CREATE_CREDENTIAL
| | | | DROP_CREDENTIAL
| | | DDL_CRYPTOGRAPHIC_PROVIDER_EVENTS
| | | | ALTER_CRYPTOGRAPHIC_PROVIDER
| | | | CREATE_CRYPTOGRAPHIC_PROVIDER
| | | | DROP_CRYPTOGRAPHIC_PROVIDER
| | | DDL_GDR_SERVER_EVENTS
| | | | DENY_SERVER
| | | | GRANT_SERVER
| | | | REVOKE_SERVER
| | | DDL_LOGIN_EVENTS
| | | | ALTER_LOGIN
| | | | CREATE_LOGIN
| | | | DROP_LOGIN
| | | DDL_SERVER_AUDIT_EVENTS
| | | | ALTER_SERVER_AUDIT
| | | | CREATE_SERVER_AUDIT
| | | | DROP_SERVER_AUDIT
| | | DDL_SERVER_AUDIT_SPECIFICATION_EVENTS
| | | | ALTER_SERVER_AUDIT_SPECIFICATION
| | | | CREATE_SERVER_AUDIT_SPECIFICATION
| | | | DROP_SERVER_AUDIT_SPECIFICATION
| | | DDL_SERVICE_MASTER_KEY_EVENTS
| | | | ALTER_SERVICE_MASTER_KEY
| | | DROP_SERVER_ROLE
| | | DROP_SERVER_ROLE_MEMBER
More information about events is available there: DDL Event Groups
All information about event presented in xml, that we could get using EVENTDATA() function. For more information, please, checkout XSD of EVENTDATA
Lets try to create it:
CREATE TABLE dbo.DdlHistory
(
DdlHistoryId BIGINT NOT NULL IDENTITY PRIMARY KEY,
EventType NVARCHAR(128) NOT NULL,
PostTime DATETIME NOT NULL,
LoginName NVARCHAR(128),
UserName NVARCHAR(128),
DatabaseName NVARCHAR(128),
SchemaName NVARCHAR(128),
ObjectName NVARCHAR(128),
ObjectType NVARCHAR(128),
Command NVARCHAR(MAX)
) WITH (DATA_COMPRESSION = PAGE);
GO
CREATE TRIGGER WriteDdlHistory ON DATABASE
FOR DDL_DATABASE_LEVEL_EVENTS AS
BEGIN
SET NOCOUNT ON;
DECLARE @data XML,
@eventType NVARCHAR(128),
@postTime DATETIME,
@loginName NVARCHAR(128),
@userName NVARCHAR(128),
@databaseName NVARCHAR(128),
@schemaName NVARCHAR(128),
@objectName NVARCHAR(128),
@objectType NVARCHAR(128),
@command NVARCHAR(MAX);
SET @data = EVENTDATA();
SET @eventType = @data.value('(/EVENT_INSTANCE/EventType)[1]', 'NVARCHAR(128)');
SET @postTime = @data.value('(/EVENT_INSTANCE/PostTime)[1]', 'DATETIME');
SET @loginName = @data.value('(/EVENT_INSTANCE/LoginName)[1]', 'NVARCHAR(128)');
SET @userName = @data.value('(/EVENT_INSTANCE/UserName)[1]', 'NVARCHAR(128)');
SET @databaseName = @data.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'NVARCHAR(128)');
SET @schemaName = @data.value('(/EVENT_INSTANCE/SchemaName)[1]', 'NVARCHAR(128)');
SET @objectName = @data.value('(/EVENT_INSTANCE/ObjectName)[1]', 'NVARCHAR(128)');
SET @objectType = @data.value('(/EVENT_INSTANCE/ObjectType)[1]', 'NVARCHAR(128)');
SET @command = @data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'NVARCHAR(MAX)');
INSERT INTO dbo.DdlHistory(EventType, PostTime, LoginName, UserName, DatabaseName, SchemaName, ObjectName, ObjectType, Command)
VALUES (@eventType, @postTime, @loginName, @userName, @databaseName, @schemaName, @objectName, @objectType, @command);
END
To create this trigger in all new databases, you should to create DDL-trigger in the model database.
To compare changes I'm very suggest to use the DataGrip. To compare your should to select two changed cell of column Command and press CTRL+SHIFT+D
