SQL Server Management Studio Standard Reports – Schema Changes History

I’m continuing a series on the Standard Reports you can find in SQL Server Management Studio (SSMS).

The Schema Changes History report shows the changes you’ve made using Data Definition Language statements, similar to the Configuration Changes History report, in that it tracks changes in an instance using the Default Trace mechanism. I mentioned this in yesterday's post and promised I would cover this mechanism in a little more depth.

SQL Server records certain events about your system in a “Default Trace”, which is a binary file that watches SQL Server for certain events. It uses the SQL Trace mechanism, which you can read more about here. Although the Default Trace uses SQL Trace, for performance and privacy reasons it doesn't track every single event on the server. You can run this script to find out what it gets:

SELECT cat.name AS Category

, b.name AS EventCaptured

, c.name AS ColumnCaptured

FROM fn_trace_geteventinfo(1) AS a

INNER JOIN sys.trace_events AS b

ON a.eventid = b.trace_event_id

INNER JOIN sys.trace_columns AS c

ON a.columnid = c.trace_column_id

INNER JOIN sys.trace_categories AS cat

ON b.category_id = cat.category_id

ORDER BY Category, EventCaptured, ColumnCaptured

It also "rolls over", so everything you do on the server isn't persisted forever. Because of this It would be useful to save off or print this report from time to time, although there are more thorough methods you can use to track DDL changes. This report is a quick and easy method to do that, however.

By default this mechanism is always running. If it is turned off, you won’t have any information in this report. As I mentioned yesterday, you can find out if it is on with this script:

USE master;

EXEC sp_configure 'show advanced option', '1';

RECONFIGURE WITH OVERRIDE;

EXEC sp_configure;

GO

Look for the default trace enabled value and check to see that the run value is 1.

If it is off and you want to turn it on (check first and find out why it is off), you can use this script:

EXEC master.dbo.sp_configure 'allow updates', 1;

GO

EXEC master.dbo.sp_configure 'show advanced options', 1;

GO

EXEC master.dbo.sp_configure 'default trace enabled', 1;

GO

RECONFIGURE WITH OVERRIDE;

GO

To find this report, open SSMS, right-click a Server, and then select “Reports” and then “Standard Reports”.  This report doesn’t take long to render.

If you don't have any data in it, make sure the Default Trace is running as described above and then run this script (Note - run this on a test system only!):

CREATE DATABASE Test;

GO

USE Test;

GO

CREATE TABLE TestTable (c1 int);

GO

ALTER TABLE TestTable ADD c2 int;

GO

DROP TABLE TestTable;

GO

USE master;

GO

DROP DATABASE Test;

GO

Now you'll have some data to look at in the report.

If you want more information from the Default Trace than is showing here, you can read the entire thing with this command:

SELECT *

FROM fn_trace_gettable

('C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\log.trc', default)

GO

Basically this statement reads from a function that reads from the binary file that SQL Trace creates.