Capture Schema Change in SQL Server to maintain history

Capture Schema Change in SQL Server to maintain history

Every production database should have strict audit/history policy for schema change so that each and every change can be tracked. SQL Server 2005+ providing trigger at database level which is being called at database level every time it meets criteria.

Here in this article, I am providing with one small TSQL Script of database trigger for DDL commands which activates itself automatically as and when any DDL command will be executed against any schema ( tables, views, stored procedures, triggers, index and more) of any objects in the database where we have create the database level DDL trigger.

We will create one table in database which will capture all schema change in SQL Server after creating that table, we will create DDL database trigger which detects any change made in schema and capture it in the table we have created. You can even set an email alert to authorize person so that whenever any change happens in the schema of table, Stored Procedure, View, Index, Function or any other objects, that person(s) will get an alert in an email along saving the history in table. I have not covered that email part here in this article but as long as you are having all the values which we are going to store in table, you can implement simple email script for your need.

By the way, I have already written one article on DDL trigger few years back but you can consider this article as an improved version, you can visit previous article here.

Let us now create the table which will store the history of schema change in the database.

Note: I have used “AdventureWorks2012” database for this article, you can use the same or else you can have your own database, code won’t have much changes.

USE [AdventureWorks2012]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [CaptureSchemaChange](
[EventType] [nvarchar](max) NULL,
[SchemaName] [nvarchar](max) NULL,
[ObjectName] [nvarchar](max) NULL,
[ObjectType] [nvarchar](max) NULL,
[EventDate] [datetime] NULL,
[SystemUser] [varchar](100) NULL,
[CurrentUser] [varchar](100) NULL,
[OriginalUser] [varchar](100) NULL,
[DatabaseName] [varchar](100) NULL,
[CommandText] [nvarchar](max) NULL,
[EventData] xml NULL,
[HostName] [varchar](50) NULL
 )
GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [CaptureSchemaChange] ADD  DEFAULT (host_name()) FOR [HostName]
GO

Once you are ready with table given in above script, we will now create DDL database trigger in SQL Server 2012 database.

USE [AdventureWorks2012]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TRIGGER [trgCaptureSchemaChange] ON DATABASE
FOR DDL_DATABASE_LEVEL_EVENTS
AS

SET NOCOUNT ON

DECLARE @EventType NVARCHAR(MAX)
DECLARE @SchemaName NVARCHAR(MAX)
DECLARE @ObjectName NVARCHAR(MAX)
DECLARE @ObjectType NVARCHAR(MAX)
DECLARE @DBName VARCHAR(100)
DECLARE @Message VARCHAR(1000)
DECLARE @TSQL NVARCHAR(MAX)

SELECT
@EventType = EVENTDATA().value('(/EVENT_INSTANCE/EventType)[1]','NVARCHAR(MAX)')
,@SchemaName = EVENTDATA().value('(/EVENT_INSTANCE/SchemaName)[1]','NVARCHAR(MAX)')
,@ObjectName = EVENTDATA().value('(/EVENT_INSTANCE/ObjectName)[1]','NVARCHAR(MAX)')
,@ObjectType = EVENTDATA().value('(/EVENT_INSTANCE/ObjectType)[1]','NVARCHAR(MAX)')
,@DBName = EVENTDATA().value('(/EVENT_INSTANCE/DatabaseName)[1]','NVARCHAR(MAX)')
,@TSQL = EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','NVARCHAR(MAX)')

IF @SchemaName = ' '
BEGIN
SELECT
@SchemaName = default_schema_name
FROM
sys.sysusers SysUser
INNER JOIN
sys.database_principals Pri
ON
SysUser.uid = Pri.principal_id
WHERE
SysUser.name = CURRENT_USER
END

INSERT INTO [CaptureSchemaChange]
([EventType]
,[SchemaName]
,[ObjectName]
,[ObjectType]
,[EventDate]
,[SystemUser]
,[CurrentUser]
,[OriginalUser]
,[DatabaseName]
,[CommandText]
,[EventData]
)
SELECT
@EventType
, @SchemaName
, @ObjectName
, @ObjectType
, getdate()
, SUSER_SNAME()
, CURRENT_USER
, ORIGINAL_LOGIN()
, @DBName
, @TSQL
, EVENTDATA()
GO

SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER OFF
GO

ENABLE TRIGGER [trgCaptureSchemaChange] ON DATABASE
GO

So, you have now successfully created DDL Database Trigger in SQL Server 2012. You schema is secured now, If anything will be changed, you will get an email, if email alert is set inside the trigger otherwise you can manually check the history in “CaptureSchemaChange” table we have created.

If you like this article, do like “Extreme-Advice” page in Facebook.

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

Sharing IT tips at “Quick Learn

Note: Microsoft Books online is a default reference of all articles.

3 thoughts on “Capture Schema Change in SQL Server to maintain history

  1. Pingback: Monitor user login connected to SQL Server

Comments are closed.