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
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
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.
