locked
schema changes history by T-SQL RRS feed

  • Question

  • can we retrieve schema changes history by T-SQL Statement?? and between any two dates???
    --------------------------------- Devender Bijania
    Friday, December 31, 2010 10:31 AM

Answers

  • Hi,

    Just adding to above, the DEFAULT trace are confgured to rollover to new file after 20 MB size is reached.  New trace file will be created for trace. New trace file gets created when service starts.

    Just, in case if you are using sql 2008, you can have a look at SQL Server Management Studio Standard Reports –> Schema Changes History.

    If you want to get information for all the rollover files in trace, fn_trace_gettable reads all rollover files until it reaches the end of the trace.

    This is taken from profiler while running schema histoty report

    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) 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
    

    -Chintak
    • Proposed as answer by Naomi N Friday, December 31, 2010 4:56 PM
    • Marked as answer by Kalman Toth Sunday, January 2, 2011 10:55 AM
    Friday, December 31, 2010 1:01 PM

All replies

  • Yes. If you are using SQL SERVER 2008 and CDC is active.

    Otherwise, you can get the object names from sys.objects using modified_date column.


    Please visit my Blog for some easy and often used t-sql scripts
    Friday, December 31, 2010 11:00 AM
  • I have sql 2008 standard edition... not supporting CDC.

    by the way.. Can you give me some more idea abt CDC.... how can we track the things by this feature??

     


    --------------------------------- Devender Bijania
    Friday, December 31, 2010 11:11 AM
  • through SYS.OBJECTS .. how can we find, who(user) made the changes....??
    --------------------------------- Devender Bijania
    Friday, December 31, 2010 11:14 AM
  • Hi Devender,

    In sql server 2005 and 2008, there is a default trace which is set to on by default. This captures the schema change information.

    you can get location of trace file by following query

    SELECT traceidvalue FROM [fn_trace_getinfo](NULL)
    WHERE [property] 2;

    then you can use fn_trace_gettable to find

    SELECT StartTime,
    FROM [fn_trace_gettable]('<<location from above query>>'DEFAULT
    )


    -Chintak
    Friday, December 31, 2010 11:21 AM
  • You won't be able to see which user made the changes, unless you create some custom DDL triggers.

    Have a look in BOL for DDL triggers, you could create something that writes to an audit table when tables, procs, functions are altered.

    I don't think that CDC is what you are after as it is for data changes rather than schema changes, but you can read about it here: http://msdn.microsoft.com/en-us/library/bb522489.aspx

    You can read about DDL triggers here: http://msdn.microsoft.com/en-us/library/ms190989.aspx


    If you find this helpful, please mark the post as helpful,
    If you think this solves the problem, please propose or mark it an an answer.

    Please provide details on your SQL Server environment such as version and edition, also DDL statements for tables when posting T-SQL issues

    Richard Douglas
    My Blog: Http://SQL.RichardDouglas.co.uk
    Twitter: @SQLRich
    Friday, December 31, 2010 11:21 AM
  • Hi Chintak... Thnx for your gud response.

    i got the tracefile but im unable to filter which transactions are schema-changes transactions. Plz give some more hint.


    --------------------------------- Devender Bijania
    Friday, December 31, 2010 11:59 AM
  • If you dont have CDC, you canb use DDL triggers to capture your schema changes. Check DDL Trigger example here;

    http://www.sqlteam.com/article/using-ddl-triggers-in-sql-server-2005-to-capture-schema-changes

    Sys.objects will not give you the User information but will tell you the last modofoed date of that object.

    for CDC, check this link;

    http://www.simple-talk.com/sql/learn-sql-server/introduction-to-change-data-capture-%28cdc%29-in-sql-server-2008/


    Please visit my Blog for some easy and often used t-sql scripts
    Friday, December 31, 2010 12:34 PM
  • Friday, December 31, 2010 12:36 PM
  • hi atif, thux for suggession...

    but Im looking for a solution without any insertion trigger.....

     


    --------------------------------- Devender Bijania
    Friday, December 31, 2010 12:40 PM
  • Its not Insertion trigger. Its DDL Trigger. Means, when ever DDL statement will be executed, this trigger will be fired. It will not fire on any Insert / update / delete.
    Please visit my Blog for some easy and often used t-sql scripts
    Friday, December 31, 2010 12:45 PM
  • Hi,

    You can use following code...

    SELECT StartTime as st,
    s.name [action],DatabaseName,ObjectName,EventSubClass,ObjectType,* 
    FROM [fn_trace_gettable]('C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Log\log_24.trc', DEFAULT) as trc
    inner join sys.trace_events s on s.trace_event_id = trc.EventClass
    where -- DatabaseName like 'Mytest' and
    EventClass in (46,164,47)
    and EventSubClass = 0
    order by st desc
    Event class 46 is for object created, 47 for drop and 164 is for object altered...

    Also, you can use fllowing query, I got this for schema change in built report

    declare @db_name table (
     row_no int identity
    , name sysname
    );
    declare @objects table (
     db_name sysname
    , ob_name sysname
    , type nvarchar(60)
    , date datetime
    , s_name sysname
    );
    declare @cnt int;
    declare @name sysname;
    
    insert into @db_name
    select name from sys.databases where database_id <> 2 order by name desc;
    
    select @cnt = count(*) from @db_name;
    while @cnt > 0
    begin 
     select @name = name from @db_name where row_no = @cnt;
     insert into @objects
     exec('use ['+@name+']; SELECT '''+@name+''' as db_name,o.name as object_name,o.type_desc, o.modify_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);')
     set @cnt = @cnt-1;
    end
    
    select 1 as l1
    , * 
    from @objects 
    order by date desc, db_name, s_name, ob_name
    

     


    -Chintak
    • Edited by Chintak Chhapia Friday, December 31, 2010 1:13 PM
    • Proposed as answer by Naomi N Friday, December 31, 2010 4:56 PM
    Friday, December 31, 2010 12:49 PM
  • Hi,

    Just adding to above, the DEFAULT trace are confgured to rollover to new file after 20 MB size is reached.  New trace file will be created for trace. New trace file gets created when service starts.

    Just, in case if you are using sql 2008, you can have a look at SQL Server Management Studio Standard Reports –> Schema Changes History.

    If you want to get information for all the rollover files in trace, fn_trace_gettable reads all rollover files until it reaches the end of the trace.

    This is taken from profiler while running schema histoty report

    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) 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
    

    -Chintak
    • Proposed as answer by Naomi N Friday, December 31, 2010 4:56 PM
    • Marked as answer by Kalman Toth Sunday, January 2, 2011 10:55 AM
    Friday, December 31, 2010 1:01 PM
  • > can we retrieve schema changes history by T-SQL Statement?? and between any two dates???

    There is nothing built-in for this. As suggested by others, you can use DDL triggers, but they can be disabled by anyone who want to cover his tracks.

    The correct answer to your question is that the database schema, as all other code, should be kept under version control. A version control system has all the traceability you ask for.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Links for SQL Server Books Online: SQL 2008, SQL 2005 and  SQL 2000.
    (Just click the link you need.)
    Friday, December 31, 2010 4:36 PM
  • Thnx Chintak.... your code is working and it's the solution that i want.
    --------------------------------- Devender Bijania
    Monday, January 3, 2011 4:40 AM
  • Hi Devender,

    I am glad that I can help you, but this is not my code, this is SQL server inbuilt report.


    -Chintak (My Blog)

    Monday, January 3, 2011 8:42 AM