none
Find when a column was added to a table;

    Question

  • Hi all, We found over the weekend that someone added three columns to a table, Is there a way to find out when and who added the column? I was trying to query the default trace, but am getting lot of nulls for textdata.We have backups from weekend too.
    Thanks
    Jay
    Monday, November 30, 2009 9:03 PM

All replies

  • You can have this detail from default reports.

    Right click the SQL server in SSMS and tab down to reports and select SCHEMA CHANGES HISTORY..

    You will have columns for database name , object name in which the schema was changed , type , DDL operation , time and login that performed it.But you may not be able to see the newly added COLUMN name in that table.


    Thanks, Leks
    Monday, November 30, 2009 9:11 PM
    Answerer
  • I dont see the newly added columns in the schema changes hisotry, I dont think we have a DDL trigger or anything on this server.
    Thanks
    Jay
    Monday, November 30, 2009 9:24 PM
  • Are you able to see any entry on the objectname(tablename) with ALTER EVENT on it from the report?
    Thanks, Leks
    Monday, November 30, 2009 9:46 PM
    Answerer
  • modify_date in sys.tables? Assumes the change was made using ALTER TABLE. If the table was re-created, then perhaps the create_Date column instead?
    Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi
    Monday, November 30, 2009 10:17 PM
    Moderator
  • modify_date in sys.tables? Assumes the change was made using ALTER TABLE. If the table was re-created, then perhaps the create_Date column instead?
    Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi

    The scope of the information you can get from the default trace is dependent on how the table was changed.  The following query will return any table changes in the default trace for the current database that the script is run inside of:

    DECLARE @FileName VARCHAR(MAX)  
    
    SELECT @FileName = SUBSTRING(path, 0, LEN(path)-CHARINDEX('\', REVERSE(path))+1) + '\Log.trc'  
    FROM sys.traces   
    WHERE is_default = 1;  
    
    SELECT   
    	gt.EventClass,
    	e.name as EventName,
    	gt.ObjectID,
    	gt.ObjectName,
    	gt.DatabaseName,
    	gt.SessionLoginName,
    	gt.StartTime,
    	gt.ApplicationName,
    	gt.HostName,
    	gt.NTUserName,
    	gt.NTDomainName
    FROM sys.fn_trace_gettable( @FileName, DEFAULT ) AS gt
    JOIN sys.trace_events e 
    	ON gt.EventClass = e.trace_event_id
    WHERE gt.EventClass IN 
    (
    	46, -- (Object:Created)
    	47, -- (Object:Deleted)
    	164 -- (Object:Altered)
    )
      AND ObjectType = 8277 -- '(User-defined) Table' 
      AND EventSubClass = 0
      AND DatabaseID = db_id()
    You need to capture all 3 of the Object Events in case the table was changed by CREATE/COPY/DROP/RENAME instead of ALTER TABLE.  The ObjectType column = 8277 restricts this to tables only and EventSubClass 0 has the call data for any changes.  The TextData won't be available in the default trace for these events as shown in their BOL Entries:

    http://msdn.microsoft.com/en-us/library/ms187076.aspx
    http://msdn.microsoft.com/en-us/library/ms191489.aspx
    http://msdn.microsoft.com/en-us/library/ms190722.aspx

    If this query doesn't return the information, I'm afraid that you probably won't be able to find out who made the change unless you try a third party log reader tool like ApexSQLLog and have it read your log backup files to try and figure it out.



    Jonathan Kehayias
    http://sqlblog.com/blogs/jonathan_kehayias/
    http://www.twitter.com/SQLSarg
    http://www.sqlclr.net/
    Please click the Mark as Answer button if a post solves your problem!
    Tuesday, December 01, 2009 12:45 AM
    Moderator
  • Thanks Jonathan, But the script didn't return any records  in the Database where the table was changed. I guess I will have to try the 3rd party log reader tool.
    Thanks
    Tuesday, December 01, 2009 2:05 AM
  • Your last attempt before going third party would be to do a simple filter WHERE ObjectName = 'TableName' against the default trace.  Also open the LOG folder for the SQL Instance and look at the dates on the default trace rollover files.  Either the server has a lot of activity that caused trace rollover to occur multiple times and the event data is gone for good, or the changes happened earlier than you think.  The dates on the rollover files for the default trace will tell you the answer to that question.  On my busier servers, the default trace is still weeks worth of data.
    Jonathan Kehayias
    http://sqlblog.com/blogs/jonathan_kehayias/
    http://www.twitter.com/SQLSarg
    http://www.sqlclr.net/
    Please click the Mark as Answer button if a post solves your problem!
    Tuesday, December 01, 2009 2:33 AM
    Moderator
  • That's perfect...But I want the column name as well
    Tuesday, December 01, 2009 7:45 AM
  • That's perfect...But I want the column name as well

    Setup Event Notifications to capture the events into a table with the DDL that generated it, or use DDL Triggers to capture the information.  You have to proactively plan to audit that kind of information.
    Jonathan Kehayias
    http://sqlblog.com/blogs/jonathan_kehayias/
    http://www.twitter.com/SQLSarg
    http://www.sqlclr.net/
    Please click the Mark as Answer button if a post solves your problem!
    Tuesday, December 01, 2009 1:44 PM
    Moderator
  • I tried it didn't return any records.
    Thanks
    Tuesday, December 01, 2009 2:51 PM
  • <<Setup Event Notifications to capture the events into a table with the DDL that generated it, or use DDL Triggers to capture the information.>>

    Or, if on SQL Server 2008 EE, capture this using a Server Audit.
    Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi
    Tuesday, December 01, 2009 4:08 PM
    Moderator