SQL SERVER – SSMS: Schema Change History Report

The heat is picking up and I am glad you are liking this series going so far. This particular report is close to my heart and the most recommended. At my recent trip to Delhi and the user group out there, I had the opportunity to meet a number of young DBA’s who were getting into their professional career at various organizations. I always try to persuade such groups with interesting questions to make them inquisitive about learning new concepts with SQL Server.

At this user-group session I wanted people to answer a simple question:

  1. How can I know, who created/dropped/altered the database?
  2. How can I know, who created/dropped/altered the objects?

This caught the attention of this group and I got various answers from DDL Triggers, Auditing, Error Logs, Extended Events and many more innovative response which I will refrain from disclosing his because they were really funny. All these answers were correct in a way and I had to counter them with yet another question to make them thinking.

Though your answers are correct in a way, “what is the easiest / simplest way to find this without writing a single line of code”. Now the twist made the response into something simple. And one attendee had a response stating – “why not use Profiler?”

This response stumped me totally and I said, let me achieve the same with lesser number of clicks for you. And my idea was to show them the use of SQL Server Management Studio – Schema Change History. It has interesting dimensions to examine and let me take a moment to walk them through the same.

Where to start?

The report location can be found from Server node -> Right Click -> Reports -> Standard Reports -> “Schema Changes History”.

SQL SERVER - SSMS: Schema Change History Report schema-change-history-1

Solarwinds

One of the important information worth noting here is, the report fetches information from the default trace. We have talked about default trace and how to enable it in our previous post on “Configuration Changes History” report.

If default trace is not enable then this report is smart enough to look at each database and find objects which were created or altered in last 7 days. I was not aware of this until I disable default trace to see the error in the report. To my surprise, the report still came up in some different format. Let us look at the outlook with these options.

With default trace enabled

The report when the default trace enabled is as shown below:

SQL SERVER - SSMS: Schema Change History Report schema-change-history-2

To catch the query that populates this report, I ran Profiler and here is the basic query:

SELECT FROM:: fn_trace_gettabl(e @base_tracefilename, default )
WHERE EventClass in (46,47,164) AND EventSubclass = 0 AND DatabaseID <> 2

To elaborate a bit, the EventClass 46, 47 and 164 corresponds to Object:Created, Object:Deleted and Object:Altered respectively (Refer sys.trace_events from MSDN for more info).

With default trace disabled

Now here is the twist, when Default Trace is disabled, the query which is executed in each database is shown below for reference:

SELECT o.name AS OBJECT_NAME,
o.type_desc,
o.create_date,
s.name AS schema_name
FROM sys.all_objects o
LEFT OUTER JOIN sys.schemas s
ON ( o.schema_id = s.schema_id)
WHERE create_date > ( GETDATE() - 7);

And below is the report generated by the query. It is evident from the last section of “Note” from the report that our default trace is not enabled.

SQL SERVER - SSMS: Schema Change History Report schema-change-history-3

There are two sections in the report. They are based on similar query which I pointed above with a minor difference of “create_date” and “modify_date” column as below.

SELECT o.name AS OBJECT_NAME,
o.type_desc,
o.create_date,
s.name AS schema_name
FROM sys.all_objects o
LEFT OUTER JOIN sys.schemas s
ON ( o.schema_id = s.schema_id)
WHERE modify_date > ( GETDATE() - 7);

The disadvantage of disabling default trace is that we would not be able to see any information if a database was dropped. I generally have seen this trace to be non-intrusive on most of the systems. But would love to hear from you and learn if you faced any problems with it.

Caveats with Schema Change History Report

One problem in the report is, even if one database is inaccessible, it would give error and fails to report anything for remaining databases. For illustration purposes, I made the database to norecovery state and I refreshed the report to get the below error:

SQL SERVER - SSMS: Schema Change History Report schema-change-history-4

If you ever have such situation, you can run the T-SQL query mentioned above manually on the database under question to view the changes.

Does anyone reading this post, ever disabled your Default Trace ever? Have you used this reports in your environment? Let me know your learnings.

Reference: Pinal Dave (https://blog.sqlauthority.com)

Solarwinds
,
Previous Post
Developer’s Life – Every Developer is Like Transformers
Next Post
SQL SERVER – Three Top Efficiency Features of Management Tool for SQL Server

Related Posts

29 Comments. Leave new

  • Hi Pinal,

    Thanks for all your wonderful posts including this one.

    I was connected to SQL Server 2012 – Enterprise edition and to my surprise i didn’t find any option to generate report as mentioned by you. I Rechecked it is SQL Server 2012 only.

    Do i need to make any configuration changes or something else?

    Br,
    Anil Kumar

    Reply
  • What should be the datatype for column “filesize” in mb (eg.345.45634576) in SQl server 2012

    Reply
  • How can I see who created a table which is deleted?

    Reply
  • Hi Pinal,

    Hope you good.

    I need Client Machine Id details in Schema Change Report, can you please guide and suggest please.

    Basically, I want to know that which client machine executed the DML command.

    Thanks in Advance,
    Tejas

    Reply
  • Pankaj Todkar
    March 8, 2016 4:17 pm

    declare @d1 datetime;
    declare @diff int;
    declare @curr_tracefilename varchar(500);
    declare @base_tracefilename varchar(500);
    declare @indx int ;
    declare @temp_trace table (
    obj_name nvarchar(256)
    , obj_id int
    , database_name nvarchar(256)
    , start_time datetime
    , event_class int
    , event_subclass int
    , object_type int
    , server_name nvarchar(256)
    , login_name nvarchar(256)
    , user_name nvarchar(256)
    , application_name nvarchar(256)
    , ddl_operation nvarchar(40)
    );
    select @curr_tracefilename = path from sys.traces where is_default = 1 ;
    set @curr_tracefilename = reverse(@curr_tracefilename)
    select @indx = PATINDEX(‘%\%’, @curr_tracefilename)
    set @curr_tracefilename = reverse(@curr_tracefilename)
    set @base_tracefilename = LEFT( @curr_tracefilename,len(@curr_tracefilename) – @indx) + ‘\log.trc’;

    select ObjectName
    , ObjectID
    , DatabaseName
    , StartTime
    , EventClass
    , EventSubClass
    , ObjectType
    , ServerName
    , LoginName
    , NTUserName
    , ApplicationName
    , ‘temp’
    FROM ::fn_trace_gettable( @base_tracefilename, default )
    where EventClass in (46,47,164) and EventSubclass = 0 and DatabaseID = db_id()
    AND OBJECTNAME lIKE ‘%TEST%’ — Put Your Object name Here
    order by objectname,starttime desc

    Reply
  • Pankaj Todkar
    March 8, 2016 4:38 pm

    Following Query will give Proper Detailed Information IF the SQL trace is ON

    declare @curr_tracefilename varchar(500);
    declare @base_tracefilename varchar(500);
    declare @indx int ;

    select @curr_tracefilename = path from sys.traces where is_default = 1 ;
    set @curr_tracefilename = reverse(@curr_tracefilename)
    select @indx = PATINDEX(‘%\%’, @curr_tracefilename)
    set @curr_tracefilename = reverse(@curr_tracefilename)
    set @base_tracefilename = LEFT( @curr_tracefilename,len(@curr_tracefilename) – @indx) + ‘\log.trc’;

    select
    ObjectName
    , CASE EventClass WHEN 46 THEN ‘CREATE’ WHEN 47 THEN ‘DROP’ WHEN 164 THEN ‘ALTER’ END DDLOperation
    , ObjectID
    , DatabaseName
    , StartTime
    , EventClass
    , EventSubClass
    , ObjectType
    , ServerName
    , LoginName
    , NTUserName
    , ApplicationName
    FROM ::fn_trace_gettable( @base_tracefilename, default )
    where EventClass in (46,47,164) and EventSubclass = 0 and DatabaseID = db_id()
    AND OBJECTNAME lIKE ‘%TEST%’ —- Put Your Object name Here

    Reply
    • Great. Did that come from profiler?

      Reply
      • pankaj todkar
        April 20, 2016 1:42 pm

        Yes Sir, I altered some context only for ‘eventclass’ according to requirement.
        I am trying to get information on ‘Rename of object’ as well.
        Sorry to reply late.

  • Dhananjay Phadke
    March 18, 2016 2:45 pm

    Hello Pinal , Good Day !!!

    i need small information… if someone doing changes in database… on same time is it possible to take screenshot or screens record also… from back end…

    Thank You
    Dhananjay

    Reply
  • Bikram Sarkar
    July 27, 2016 11:28 am

    Hi SIr,

    Can you help getting the Alter statement that was executed for a particular table?

    Thanks in advanced

    Reply
  • Hi Pinal,

    This is very informative seems this will generate report for a week what if someone needs exactly same report between two given dates.highly appreciated if you answer this.

    Thanks

    Reply
  • Hello All, Even though in our SQL Server , default trace is enabled, the SSMS “Schema Changes History” is showing empty. Any other lead you could recommend for getting the report? Thank you

    Reply
  • how many days data hold the schema change history report ? in my environment one of the procedure got dropped..when i checked one day after the schema change history report is showing empty server is 2008 r2

    Reply
  • Erik Berglund
    March 6, 2017 4:35 pm

    Hello All! Does anybody know if this report is high intence on memory. I’v got the error message “An error occured during client rendering — > Exceception of type “System.OutOfMermoryException’ was thrown.

    The machine is a virtual server Inte Xeon R CPU E5-2670 @ 2,60 Ghz (4 proc) and 16 GB RAM. The databases is a normal SharePoint enviroment.

    Regards
    ERIK

    Reply
  • Elaine Mayer
    July 20, 2017 8:34 pm

    When run Schema Changes History report, it will show the Login Name. So for the following query, can it be modified to include the login name?

    SELECT o.name AS OBJECT_NAME,
    o.type_desc,
    o.create_date,
    s.name AS schema_name
    FROM sys.all_objects o
    LEFT OUTER JOIN sys.schemas s
    ON ( o.schema_id = s.schema_id)
    WHERE modify_date > ( GETDATE() – 7);

    Reply
  • SELECT o.name AS OBJECT_NAME,
    o.type_desc,
    o.create_date,
    s.name AS schema_name
    FROM sys.all_objects o
    LEFT OUTER JOIN sys.schemas s
    ON ( o.schema_id = s.schema_id)
    WHERE create_date > ( GETDATE() – 7);

    Reply
  • Bhagwati Prasad
    March 21, 2018 3:47 pm

    Hi Team, can you help me when i have run Report “Schema Change History” with default date data coming in report like i have run today so data coming 3rd march 2018 to till date but suppose i want last one year and 6 month “Schema Change History” data then how can modify date and retried data… plz suggest..Thanks in advance.

    Reply
  • Hi, in my environment, the recovery model for 5 databases was set to full, but couple of days ago it was changed to simple.. I checked in error log, was able to track the spid but not the login name.. Can someone help me to identify who has changed it? No details in schema changes history as well..

    Reply
  • Does it only go back 7 days ?

    What if I have to look back for the last 30 days ? Can the schema change report help me to go back upto 30 days ?

    Reply
  • JAMES JOHN GIERCYK
    July 10, 2019 6:09 pm

    Why not just run an extended event? It allows for more customization and you are able to extend the time period as far back as you want.

    CREATE EVENT SESSION [SchemaChanges] ON SERVER
    ADD EVENT sqlserver.object_altered(
    ACTION(sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.database_name,sqlserver.nt_username,sqlserver.session_id,sqlserver.session_nt_username,sqlserver.sql_text,sqlserver.username)),
    ADD EVENT sqlserver.object_created(
    ACTION(sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.database_name,sqlserver.nt_username,sqlserver.session_id,sqlserver.session_nt_username,sqlserver.sql_text,sqlserver.username)),
    ADD EVENT sqlserver.object_deleted(
    ACTION(sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.database_name,sqlserver.nt_username,sqlserver.session_id,sqlserver.session_nt_username,sqlserver.sql_text,sqlserver.username))
    ADD TARGET package0.event_file(SET filename=N’L:\Logfiles\ExtendedEvents\SchemaChanges.xel’,max_rollover_files=(2))
    WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=OFF)
    GO

    Reply
  • JAMES JOHN GIERCYK
    July 10, 2019 6:15 pm

    Also, if you use the dbatools module in Powershell you can run the following command:

    Get-DbaSchemaChangeHistory | Out-GridView

    Reply
  • I tried to use the script as a job but the script seems wrong :

    SELECT FROM:: fn_trace_gettabl(e @base_tracefilename, default )
    WHERE EventClass in (46,47,164) AND EventSubclass = 0 AND DatabaseID

    Reply

Leave a Reply

Menu