Ever have the need to be able to track changes to the objects in your database? This code sample was provided by a friend, Josh Shilling. It provides you with a change log on your database. This will be helpful in multi developer environments when you think someone made a change to your database, but you don’t know what changes were made. So you can run Select * from dbversion.ChangeLog to see what changes have been to your db objects. I had a hard time getting the image of the results to show.
GO
/****** Object: Table [DBVersion].[ChangeLog] ******/
SET
ANSI_NULLS ON
GO
SET
QUOTED_IDENTIFIER ON
GO
SET
ANSI_PADDING ON
GO
CREATE
TABLE [DBVersion].[ChangeLog](
[LogId] [bigint] IDENTITY(1,1) NOT NULL,
[DatabaseName] [nvarchar](256) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[EventType] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[ObjectName] [varchar](256) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[ObjectType] [nvarchar](25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[SqlCommand] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[EventDate] [datetime] NOT NULL DEFAULT (getutcdate()),
[LoginName] [nvarchar](256) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
PRIMARY
KEY NONCLUSTERED
(
[LogId] ASC
)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, _DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
)
ON [PRIMARY]
GO
SET
ANSI_PADDING OFF
--Trigger
GO
/****** Object: DdlTrigger [ddltrg_ObjectRevisionHistory] ******/
SET
ANSI_NULLS ON
GO
SET
QUOTED_IDENTIFIER ON
GO
CREATE
TRIGGER [ddltrg_ObjectRevisionHistory]
ON
database
FOR
CREATE_PROCEDURE, ALTER_PROCEDURE, DROP_PROCEDURE,
CREATE_TABLE
, ALTER_TABLE, DROP_TABLE, CREATE_FUNCTION, ALTER_FUNCTION, DROP_FUNCTION,
CREATE_INDEX
, ALTER_INDEX, DROP_INDEX, CREATE_TRIGGER, ALTER_TRIGGER, DROP_TRIGGER,
CREATE_STATISTICS
, DROP_STATISTICS, UPDATE_STATISTICS, CREATE_SCHEMA, ALTER_SCHEMA,
DROP_SCHEMA
, GRANT_DATABASE, DENY_DATABASE, REVOKE_DATABASE, CREATE_USER, ALTER_USER,
DROP_USER
, CREATE_VIEW, ALTER_VIEW, DROP_VIEW
as
BEGIN
SET NOCOUNT ON
DECLARE @data XML
SET @data = EVENTDATA()
INSERT INTO DBVersion.ChangeLog
(databasename, eventtype, objectname, objecttype, sqlcommand, loginname)
VALUES
(
@data
.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'varchar(256)'),
@data
.value('(/EVENT_INSTANCE/EventType)[1]', 'varchar(50)'),
@data
.value('(/EVENT_INSTANCE/ObjectName)[1]', 'varchar(256)'),
@data
.value('(/EVENT_INSTANCE/ObjectType)[1]', 'varchar(25)'),
@data
.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'varchar(max)'),
@data
.value('(/EVENT_INSTANCE/LoginName)[1]', 'varchar(256)')
)
END
GO
SET
ANSI_NULLS OFF
GO
SET
QUOTED_IDENTIFIER OFF
GO
ENABLE
TRIGGER [ddltrg_ObjectRevisionHistory] ON DATABASE