T-SQL Script to check Schema Change History

Sometimes we need to check who execute the command like DROP\CREATE\ALTER on Databases, also wanted to know by which Login Account, on which Date and Time they have executed the command. We can check this by enabling the Audit or from Schema change History from Reports in SSMS . Sometimes schema change may shows the report history or may not show any history of reports and Auditing was not Enabled then we will get struck at that time .

We have a default trace files on Log Folder Location , these default trace files were created at the time of SQL Installation, these will capture the DDL commands what are executed on databases. Below is the script to check the DDL command executed on Databases.

declare @d1 datetime;
declare @diff int;
declare @curr_tracefilename varchar(500);
declare @base_tracefilename varchar(500);
declare @indx int ;
--shw. didn't show new user login or roles..or deleting
declare @temp_trace table (
obj_name nvarchar(256) collate database_default
, database_name nvarchar(256) collate database_default
, start_time datetime
, event_class int
, event_subclass int
, object_type int
, server_name nvarchar(256) collate database_default
, login_name nvarchar(256) collate database_default
, application_name nvarchar(256) collate database_default
, ddl_operation nvarchar(40) collate database_default
);
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';
insert into @temp_trace
select ObjectName
, DatabaseName
, StartTime
, EventClass
, EventSubClass
, ObjectType
, ServerName
, LoginName
, ApplicationName
, 'temp'
from ::fn_trace_gettable( @base_tracefilename, default )
where EventClass in (46,47,164) and EventSubclass = 0 and
DatabaseID <> 2
update @temp_trace set ddl_operation = 'CREATE' where
event_class = 46
update @temp_trace set ddl_operation = 'DROP' where
event_class = 47
update @temp_trace set ddl_operation = 'ALTER' where
event_class = 164
select @d1 = min(start_time) from @temp_trace
set @diff= datediff(hh,@d1,getdate())
set @diff=@diff/24;
select @diff as difference
, @d1 as date
, object_type as obj_type_desc
, *
from @temp_trace where object_type not in (21587)
order by start_time desc

Below is the sample output :

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.