CODEWITHSUNDEEP
sql-serversql-server-2008-r2
user3575799
user3575799

Reputation: 41

A Column has been removed from a table SQL 2008 R2

There has been an issue with one of the tables. The table has been altered and a column has been removed from the table. There are 2 chances of this happening in the database according to my knowledge.

  1. The code changes deployed by one of our colleague
  2. Some one has manually altered the table.

I need to identify if there was an alter statement run during a particular time (Maybe with user information)

Upvotes: 1

Views: 83

Answers (3)

TheGameiswar
TheGameiswar

Reputation: 28920

You also can query database transaction log .,if your database is in Full Recovery model..

I deleted a table column and below are my test results..

create table t3
(
id int,
id1 int
)

alter table t3
drop column id1

Now when i query tlog i get below info..

select suser_sname([transaction sid]),operation,[transaction name],[begin time],[lock information],description
 from fn_dblog(null,null) where [lock information] like '%place object_id of table here%'


username            operation      transaction name   begin time             lock information                              description
somename            LOP_BEGIN_XACT  ALTER TABLE      2016/08/26 18:52:52:763    NULL                                       ALTER TABLE;0x0105000000000005150000008c052d5705824bfa733f63aedf280000
null                LOP_LOCK_XACT   NULL                  NULL                 ACQUIRE_LOCK_SCH_M OBJECT: 6:1922105888:0

Upvotes: 0

bitch_cakes
bitch_cakes

Reputation: 126

You can see when the last modification of the table occurred. This should give you last change (within two weeks)

    SELECT 
    SO.Name
    ,SS.name 
    ,SO.type_desc 
    ,SO.create_date
    ,SO.modify_date 
 FROM sys.objects AS SO
INNER JOIN sys.schemas AS SS 
    ON SS.schema_id = SO.schema_id 
WHERE DATEDIFF(D,modify_date, GETDATE()) < 14
AND TYPE IN ('P','U')

Upvotes: 0

Dan Guzman
Dan Guzman

Reputation: 46231

You might be able to glean who made the schema change from the default trace:

--query all default trace rollover files for schema modifications
SELECT  
     trace.DatabaseName
    ,trace.ObjectName
    ,te.name AS EventName
    ,tsv.subclass_name
    ,trace.EventClass
    ,trace.EventSubClass
    ,trace.StartTime
    ,trace.EndTime
    ,trace.NTDomainName
    ,trace.NTUserName
    ,trace.HostName
    ,trace.ApplicationName
    ,trace.Spid
FROM (SELECT REVERSE(STUFF(REVERSE(path), 1, CHARINDEX(N'\', REVERSE(path)), '')) + N'\Log.trc' AS path
    FROM sys.traces WHERE is_default = 1) AS default_trace_path
CROSS APPLY fn_trace_gettable(default_trace_path.path, DEFAULT) AS trace
JOIN sys.trace_events AS te ON 
    trace.EventClass=te.trace_event_id
JOIN sys.trace_subclass_values AS tsv ON
    tsv.trace_event_id = EventClass
    AND tsv.subclass_value = trace.EventSubClass
WHERE te.name = N'Object:Altered'
    AND tsv.subclass_name = 'Commit'
ORDER BY trace.StartTime;

Upvotes: 4

Related Questions