SQL Server Management Studio Standard Reports – Schema Changes History

SQL Server Management Studio Standard Reports – Schema Changes History

Rate This
  • Comments 2

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.

Heading

Description

Database Name

Shows the name of the database where the object was changed.

Object Name

Shows the name of the database object that was changed.

Type

Shows the database object type that was operated on.

DDL Operation

Shows whether the CREATE, ALTER or DROP action was initiated.

Time

Shows the date and time when the DDL was implemented.

Login Name Shows the account that was used to make the change.  This can be less useful if you allow multiple people to use the same administrative account, so it’s a best practice for each administrator to have their own account.

 

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. 

 

 

Leave a Comment
  • Please add 4 and 1 and type the answer here:
  • Post
Page 1 of 1 (2 items)