SQL Server Audit Schema Changes – Part 1: DDL Changes Logged to a Table Using Service Broker

Description:
In this first part of the SQL Server Audit Schema Changes series, we dive into how to track DDL (Data Definition Language) changes—such as CREATE, ALTER, and DROP statements—by capturing them and logging to a custom table. Learn how to leverage SQL Server’s native features, including DDL triggers and Service Broker, to build a lightweight, asynchronous auditing solution that doesn’t compromise performance. This post walks you through the architecture, setup, and practical implementation, laying the foundation for robust schema change tracking in your SQL Server environment.

--Step 1 Creat Schema

CREATE SCHEMA [SchemaAudit]
GO

--Step 2 Create Tables
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [SchemaAudit].[DBNotification](
	[DatabaseName] [nvarchar](128) NOT NULL,
	[Notification_recipient_email_address_list] [nvarchar](2048) NOT NULL,
	[LastUpdatedUser] [nvarchar](128) NOT NULL,
	[LastUpdatedDateTime] [datetime] NOT NULL,
	[LastRunDate] [datetime2](7) NULL,
 CONSTRAINT [PK_DBNotificationEmailAddresses] PRIMARY KEY CLUSTERED 
(
	[DatabaseName] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [SchemaAudit].[EventData](
	[EventID] [int] IDENTITY(1,1) NOT NULL,
	[EventDataXML] [xml] NOT NULL,
	[LastRunDate] [datetime2](7) NULL,
 CONSTRAINT [PK_SchemaAudit_EventData] PRIMARY KEY CLUSTERED 
(
	[EventID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO


SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [SchemaAudit].[Events](
	[EventID] [int] NOT NULL,
	[EventDate] [datetime] NOT NULL,
	[LoginID] [int] NOT NULL,
	[ObjectID] [int] NOT NULL,
	[EventTypeID] [int] NOT NULL,
	[LastRunDate] [datetime2](7) NULL,
 CONSTRAINT [PK_SchemaAudit_Events] PRIMARY KEY NONCLUSTERED 
(
	[EventID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [SchemaAudit].[EventTypes](
	[EventTypeID] [int] IDENTITY(1,1) NOT NULL,
	[EventType] [varchar](50) NOT NULL,
	[ObjectType] [varchar](50) NULL,
 CONSTRAINT [PK_SchemaAudit_EventTypes] PRIMARY KEY CLUSTERED 
(
	[EventTypeID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [SchemaAudit].[ExcludedTSQLs](
	[Exclude_TSQL_Command_Pattern] [nvarchar](256) NOT NULL,
	[LastUpdatedUser] [nvarchar](128) NOT NULL,
	[LastUpdatedDateTime] [datetime] NOT NULL,
 CONSTRAINT [PK__Excluded__B9A8A92D10AB74EC] PRIMARY KEY CLUSTERED 
(
	[Exclude_TSQL_Command_Pattern] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO


SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [SchemaAudit].[ExcludeLogins](
	[LoginID] [int] IDENTITY(1,1) NOT NULL,
	[LoginName] [nvarchar](128) NOT NULL,
 CONSTRAINT [PK_SchemaAudit_ExcludeLogins] PRIMARY KEY CLUSTERED 
(
	[LoginID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO


SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [SchemaAudit].[Logins](
	[LoginID] [int] IDENTITY(1,1) NOT NULL,
	[LoginName] [nvarchar](128) NOT NULL,
 CONSTRAINT [PK_SchemaAudit_Logins] PRIMARY KEY CLUSTERED 
(
	[LoginID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO


SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [SchemaAudit].[Objects](
	[ObjectID] [int] IDENTITY(1,1) NOT NULL,
	[ObjectType] [varchar](50) NOT NULL,
	[ServerName] [nvarchar](31) NOT NULL,
	[DatabaseName] [nvarchar](128) NOT NULL,
	[SchemaName] [nvarchar](128) NOT NULL,
	[ObjectName] [nvarchar](128) NOT NULL,
	[TargetObjectID] [int] NULL,
	[LastRunDate] [datetime2](7) NULL,
 CONSTRAINT [PK_SchemaAudit_Objects] PRIMARY KEY CLUSTERED 
(
	[ObjectID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [SchemaAudit].[DBNotification] ADD  CONSTRAINT [DF_DBNotificationEmailAddresses_Notification_recipient_email_address_list]  DEFAULT (N'email') FOR [Notification_recipient_email_address_list]
GO

ALTER TABLE [SchemaAudit].[DBNotification] ADD  CONSTRAINT [DF_DBNotificationEmailAddresses_LastUpdatedUser]  DEFAULT (original_login()) FOR [LastUpdatedUser]
GO

ALTER TABLE [SchemaAudit].[DBNotification] ADD  CONSTRAINT [DF_DBNotificationEmailAddresses_LastUpdatedDateTime]  DEFAULT (getdate()) FOR [LastUpdatedDateTime]
GO

ALTER TABLE [SchemaAudit].[Events] ADD  CONSTRAINT [DF_SchemaAudit_Events_EventDate]  DEFAULT (getdate()) FOR [EventDate]
GO

ALTER TABLE [SchemaAudit].[ExcludedTSQLs] ADD  CONSTRAINT [DF_ExcludedTSQLs_LastUpdatedUser]  DEFAULT (original_login()) FOR [LastUpdatedUser]
GO

ALTER TABLE [SchemaAudit].[ExcludedTSQLs] ADD  CONSTRAINT [DF_ExcludedTSQLs_LastUpdatedDateTime]  DEFAULT (getdate()) FOR [LastUpdatedDateTime]
GO

ALTER TABLE [SchemaAudit].[Objects] ADD  CONSTRAINT [DF__ChangeEve__Objec__32CB82C6]  DEFAULT (N'') FOR [ObjectName]
GO

ALTER TABLE [SchemaAudit].[Events]  WITH CHECK ADD  CONSTRAINT [FK_SchemaAudit_Events_EventData] FOREIGN KEY([EventID])
REFERENCES [SchemaAudit].[EventData] ([EventID])
GO

ALTER TABLE [SchemaAudit].[Events] CHECK CONSTRAINT [FK_SchemaAudit_Events_EventData]
GO

ALTER TABLE [SchemaAudit].[Events]  WITH CHECK ADD  CONSTRAINT [FK_SchemaAudit_Events_EventTypeID] FOREIGN KEY([EventTypeID])
REFERENCES [SchemaAudit].[EventTypes] ([EventTypeID])
GO

ALTER TABLE [SchemaAudit].[Events] CHECK CONSTRAINT [FK_SchemaAudit_Events_EventTypeID]
GO

ALTER TABLE [SchemaAudit].[Events]  WITH CHECK ADD  CONSTRAINT [FK_SchemaAudit_Events_LoginID] FOREIGN KEY([LoginID])
REFERENCES [SchemaAudit].[Logins] ([LoginID])
GO

ALTER TABLE [SchemaAudit].[Events] CHECK CONSTRAINT [FK_SchemaAudit_Events_LoginID]
GO

ALTER TABLE [SchemaAudit].[Events]  WITH CHECK ADD  CONSTRAINT [FK_SchemaAudit_Events_ObjectID] FOREIGN KEY([ObjectID])
REFERENCES [SchemaAudit].[Objects] ([ObjectID])
GO

ALTER TABLE [SchemaAudit].[Events] CHECK CONSTRAINT [FK_SchemaAudit_Events_ObjectID]
GO

ALTER TABLE [SchemaAudit].[Objects]  WITH CHECK ADD  CONSTRAINT [FK_SchemaAudit_Objects_Objects] FOREIGN KEY([TargetObjectID])
REFERENCES [SchemaAudit].[Objects] ([ObjectID])
GO

ALTER TABLE [SchemaAudit].[Objects] CHECK CONSTRAINT [FK_SchemaAudit_Objects_Objects]
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Last updated user login name.' , @level0type=N'SCHEMA',@level0name=N'SchemaAudit', @level1type=N'TABLE',@level1name=N'DBNotification', @level2type=N'COLUMN',@level2name=N'LastUpdatedUser'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Last updated date time.' , @level0type=N'SCHEMA',@level0name=N'SchemaAudit', @level1type=N'TABLE',@level1name=N'DBNotification', @level2type=N'COLUMN',@level2name=N'LastUpdatedDateTime'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'TSQL command pattern that should be excluded from schema change notification email.' , @level0type=N'SCHEMA',@level0name=N'SchemaAudit', @level1type=N'TABLE',@level1name=N'ExcludedTSQLs', @level2type=N'COLUMN',@level2name=N'Exclude_TSQL_Command_Pattern'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Last updated user login name.' , @level0type=N'SCHEMA',@level0name=N'SchemaAudit', @level1type=N'TABLE',@level1name=N'ExcludedTSQLs', @level2type=N'COLUMN',@level2name=N'LastUpdatedUser'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Last updated date time.' , @level0type=N'SCHEMA',@level0name=N'SchemaAudit', @level1type=N'TABLE',@level1name=N'ExcludedTSQLs', @level2type=N'COLUMN',@level2name=N'LastUpdatedDateTime'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Object type, such as TABLE.' , @level0type=N'SCHEMA',@level0name=N'SchemaAudit', @level1type=N'TABLE',@level1name=N'Objects', @level2type=N'COLUMN',@level2name=N'ObjectType'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Name of SQL Server instance on which the change event occurred.' , @level0type=N'SCHEMA',@level0name=N'SchemaAudit', @level1type=N'TABLE',@level1name=N'Objects', @level2type=N'COLUMN',@level2name=N'ServerName'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Name of the database in which the table schema was changed.' , @level0type=N'SCHEMA',@level0name=N'SchemaAudit', @level1type=N'TABLE',@level1name=N'Objects', @level2type=N'COLUMN',@level2name=N'DatabaseName'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Schema name.' , @level0type=N'SCHEMA',@level0name=N'SchemaAudit', @level1type=N'TABLE',@level1name=N'Objects', @level2type=N'COLUMN',@level2name=N'SchemaName'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Name of the object on which the change event occurred.' , @level0type=N'SCHEMA',@level0name=N'SchemaAudit', @level1type=N'TABLE',@level1name=N'Objects', @level2type=N'COLUMN',@level2name=N'ObjectName'
GO


See Part 2