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:
- How can I know, who created/dropped/altered the database?
- 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”.
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:
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.
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:
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)
30 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
What should be the datatype for column “filesize” in mb (eg.345.45634576) in SQl server 2012
How can I see who created a table which is deleted?
When it was created? you can see that also in default trace, if data is available.
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
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
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
Great. Did that come from profiler?
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.
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
Screenshot? You can take profiler to record all the statements being run.
Hi SIr,
Can you help getting the Alter statement that was executed for a particular table?
Thanks in advanced
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
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
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
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
Can you run profiler and run the same query in SSMS to check how many rows?
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);
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);
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.
You need to implement your own auditing. Default trace is not for this purpose.
Thank you so much for 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..
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 ?
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
Also, if you use the dbatools module in Powershell you can run the following command:
Get-DbaSchemaChangeHistory | Out-GridView
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