Home > Management Studio, SQL Configuration, SQLServer, SQLServer 2005, SQLServer 2008, SQLServer 2008 R2 > SQL Server – Management Studio – Schema Changes History Report

SQL Server – Management Studio – Schema Changes History Report


Schema Changes History Report produces the list of changes done using DDL. It pulls this information from the Default Trace.

You can access the report from Management Studio:

1. Right Click on Database, for which you want to view the report,

2. Go to Reports > Standard Reports > Schema Changes History,

image

3. Schema Changes History report will open in a new window,

image

As you can see from the report, not all information is traced, for example, it traces ALTER, but it does not trace what was ALTERed about the object. i.e. ADD/DROP COLUMN.

You can also read the default trace using command:

— © 2011 – Vishal (http://SqlAndMe.com)

 

SELECT *

FROM   fn_trace_gettable

       ('C:Program FilesMicrosoft SQL Server' +

        'MSSQL10_50.MSSQLSERVERMSSQLLoglog.trc',

       default)

Default Trace does not track every single event on the Server, you can get a list of Events traced by Default Trace using below script:

SELECT CAT.Name AS Category, EVT.Name AS EventCaptured,

              COL.name AS ColumnCaptured

FROM   fn_trace_geteventinfo(1) AS TRA

INNER JOIN sys.trace_events AS EVT

       ON TRA.EventID = EVT.Trace_Event_ID

INNER JOIN sys.trace_columns AS COL

       ON TRA.ColumnID = COL.Trace_Column_ID

INNER JOIN sys.trace_categories AS CAT

       ON EVT.Category_ID = CAT.category_id

ORDER BY (1), (2), (3)

The main limitation of Default Trace is that it rolls over, so everything in trace won’t persist. It discards older data once it reaches the size limit.

Hope This Helps!

Vishal

  1. No comments yet.
  1. No trackbacks yet.