In many application the database structure can be changed by the user ex. CRM or Reporting. In such situation we may want to know such changes was maded. In SQL SERVER 2008 we have a possibility to react when DML (Data Manipulation Language) AND DDL (Data Definition Language) execution occured in out database. So try to log it.
Firstly we want to Create a single table named 'DatabaseChangesLog':
use model;
GO
CREATE SCHEMA LOGS
CREATE TABLE DatabaseChangeLogs
(
EventId int Identity Primary Key,
EventDate datetime2 Constraint DF_DefaultLogDate DEFAULT(sysdatetime()),
EventType nvarchar(100) NOT NULL,
UserName nvarchar(1050) NOT NULL,
Command nvarchar(max) NOT NULL
)
GO
Note that we creata this table in model databse. This means that each newly created database will has this table after creation. For existing databases You need to run this script manullay.
Now when we have appropriate table we can insert record to. We are able to detect each of DML changes using database trigger which is a new feauture in SQL Server 2008. Such trigger may react for more than INSERT, UPDATE and DELETE statments. Whole list of 'events' can be obtained by the executing following query: SELECT * FROM sys.trigger_event_types; .
CREATE TRIGGER StructureLogTrigger
ON DATABASE
FOR DDL_DATABASE_LEVEL_EVENTS AS
DECLARE @data XML;
SET @data = EVENTDATA();
INSERT LOGS.DatabaseChangeLogs (EventType, UserName , Command )
VALUES
(@data.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)'),
CONVERT(nvarchar(100), CURRENT_USER),
@data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(2000)') ) ;
GO
Now each changes will be logged directly to out table.
Made some changes and take a look.
Thanks
Links:
Firstly we want to Create a single table named 'DatabaseChangesLog':
use model;
GO
CREATE SCHEMA LOGS
CREATE TABLE DatabaseChangeLogs
(
EventId int Identity Primary Key,
EventDate datetime2 Constraint DF_DefaultLogDate DEFAULT(sysdatetime()),
EventType nvarchar(100) NOT NULL,
UserName nvarchar(1050) NOT NULL,
Command nvarchar(max) NOT NULL
)
GO
Note that we creata this table in model databse. This means that each newly created database will has this table after creation. For existing databases You need to run this script manullay.
Now when we have appropriate table we can insert record to. We are able to detect each of DML changes using database trigger which is a new feauture in SQL Server 2008. Such trigger may react for more than INSERT, UPDATE and DELETE statments. Whole list of 'events' can be obtained by the executing following query: SELECT * FROM sys.trigger_event_types; .
CREATE TRIGGER StructureLogTrigger
ON DATABASE
FOR DDL_DATABASE_LEVEL_EVENTS AS
DECLARE @data XML;
SET @data = EVENTDATA();
INSERT LOGS.DatabaseChangeLogs (EventType, UserName , Command )
VALUES
(@data.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)'),
CONVERT(nvarchar(100), CURRENT_USER),
@data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(2000)') ) ;
GO
Now each changes will be logged directly to out table.
Made some changes and take a look.
Thanks
Links: