Click here to Skip to main content
14,689,511 members
Articles » Database » Database » SQL
Tip/Trick
Posted 21 Oct 2013

Stats

15.8K views
31 bookmarked

Monitor Database Changes

Rate me:
Please Sign up or sign in to vote.
5.00/5 (13 votes)
21 Oct 2013CPOL
This how you can monitor your database changes

Introduction 

Project manager/ Team leader would like to know the changes being done on database tables, views , procedures, etc. by his/her team members. 

Based on my search I implemented a solution and providing in a form of a trick so that others can use it without wasting their time behind it.  

This article can help to such users.

Using the code

Here are the Steps which you will have to follow in order to achieve the purpose. 

Step : 1 Generate table for database schema changes capture. 

Script for table is given here,

USE [Your Database Name]

GO

/****** Object:  Table [dbo].[CaptureSchemaChange]    Script Date: 7/1/2013 3:30:12 PM ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

CREATE TABLE [dbo].[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

) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

SET ANSI_PADDING OFF

GO

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

GO

Step : 2 Create trigger on database

USE [Your Database Name] 

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TRIGGER [trgCaptureDatabaseChange] 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

Step : 3 Enable trigger

GO

ENABLE TRIGGER [trgCaptureDatabaseChange] ON DATABASE

GO

Snap-shot of the result

Image 1

Points of Interest 

One can know the changes done by particular users.

Additionally, 

One can make a job to send notification(daily, weekly, monthly) including the result of the table to the higer authority. So a concerned person can come to know about the changes done by the users into his project database. 

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author


Comments and Discussions

 
QuestionDoesn't seem to be working Pin
Member 1172307129-Jun-17 8:12
MemberMember 1172307129-Jun-17 8:12 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.