Schema Changes History Report in SQL Server 2008 | SansSQL

Monday, September 13, 2010

Schema Changes History Report in SQL Server 2008

In one of my previous posts "Find out who has changed what" I had explained how to find out who has changed what in SQL server when the trace is enabled.

In SQL Server 2008, the Default trace is enabled by default during the installation and this default trace in SQL Server 2008 acts as a Blackbox for the SQL Server. Hence it is also called as "Black Box" in SQL Server 2008 and  the default trace cannot be stopped by using the Query "Exec sp_trace_setstatus 'Trace_id',0 --0 stops the trace " . This has to be stopped using the sp_configure.
To Use this report, Just right Click on the database and choose the option "Reports" and Select "Standard Reports" and choose "Schema Changes History".

To Test this, I executed the below query on AdventureWorks Database. I have also included the Statements "Waitfor Delay" to know the Time Difference.

Use AdventureWorks
Go

Create Table Tbl_TestSchemaChangesHistoryReport
( Id int,
Name Varchar(50))

Waitfor Delay '00:01:00' -- to know the time difference

Alter table Tbl_TestSchemaChangesHistoryReport Add Address_Column varchar(100)

Waitfor Delay '00:01:00' -- to know the time difference

Drop table Tbl_TestSchemaChangesHistoryReport 

And here is the results.

No comments:

Post a Comment