/**********************************************************************************
Here's a script that we use on SQL Servers at our client sites.
This will create a DDL trigger that traps the creation and modification of all the most significant
SQL Server object types in a database occurring on one database,
like CREATE TABLE, ALTER VIEW, and DROP PROC, etc..., and will log information about what was done,
when, and by whom. The table created to store this log can be queried and reported on
to track the history of table creation and modification in your database.
Run this script against any database whose object modification history you would like to track.
A Reporting Services report to display this history is included
in your download from The Lytic Group as a .rdl file
that you can add to any SSRS project in Visual Studio or that you can deploy
directly to a SQL Server Reporting Services server.
***********************************************************************************/
IF EXISTS (SELECT 0 FROM sys.tables WHERE name='tblDDLLog')
DROP TABLE dbo.tblDDLLog
GO
--Create the table to store all the table modification events
CREATE TABLE dbo.tblDDLLog
(EventID int IDENTITY(1,1)
,SPID int
,EventDate datetime
,EventType varchar(200)
,SQLText varchar(max)
,DatabaseName sysname
,SchemaName sysname
,ObjectName sysname
,LoginName sysname
,UserName sysname
)
GO
--Drop the DDL trigger if it already exists
IF EXISTS (SELECT 0 FROM sys.server_triggers
WHERE name = 'tr_DDLLogEvents')
DROP TRIGGER tr_DDLLogEvents
ON ALL SERVER;
GO
--Create the trigger.
CREATE TRIGGER tr_DDLLogEvents
ON DATABASE
FOR DDL_TABLE_EVENTS, DDL_VIEW_EVENTS, DDL_PROCEDURE_EVENTS, DDL_FUNCTION_EVENTS
--If you want to include any of the following events in your log,
--remove just the '--' at the start of that event's line:
-- ,DDL_INDEX_EVENTS
-- ,DDL_STATISTICS_EVENTS
-- ,DDL_TRIGGER_EVENTS
-- ,DDL_SYNONYM_EVENTS
AS
DECLARE @data xml
SET @data =EVENTDATA()
INSERT INTO dbo.tblDDLLog(SPID
,EventDate
,EventType
,SQLText
, DatabaseName
, SchemaName
, ObjectName
, LoginName
, UserName)
VALUES( @@SPID
,@data.value ('(/EVENT_INSTANCE/PostTime)[1]', 'datetime')
,@data.value ('(/EVENT_INSTANCE/EventType)[1]', 'varchar(200)')
,@data.value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]', 'varchar(max)')
,@data.value ('(/EVENT_INSTANCE/DatabaseName)[1]', 'varchar(200)')
,@data.value ('(/EVENT_INSTANCE/SchemaName)[1]', 'varchar(200)')
,@data.value ('(/EVENT_INSTANCE/ObjectName)[1]', 'varchar(200)')
,SUSER_SNAME()
,CURRENT_USER
)
GO
--This stored procedure will be referenced by the report you downloaded with this script
CREATE PROC uspDDLHistory
@prmStartDate datetime=GetDate(), @prmEndDate datetime=GETDATE()
AS
--Retrieve all logged DDL activity between the date parameters
SELECT [EventID]
,[SPID]
,[EventDate]
,[EventType]
,[SQLText]
,[DatabaseName]
,[SchemaName]
,[ObjectName]
,[LoginName]
,[UserName]
FROM [tblDDLLog]
--make sure to include everything that happened on the EndDate, regardless of its time
WHERE EventDate BETWEEN @prmStartDate AND (CONVERT(varchar(12),@prmEndDate,112)+ ' 11:59:59')
GO