Table Alter History - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

Table Alter History

Author

Topic

ServerTeam
Yak Posting Veteran

83 Posts

Posted - 2008-06-26 : 08:44:25

Hi Gurus,

One of my table is been altered by some one.

How can i know history of my alter table ?

Is their any systable for this ?

Thanks,
ServerTeam

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2008-06-26 : 09:09:41

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

maninder
Posting Yak Master

100 Posts

Posted - 2008-06-26 : 11:36:04

check modify_date in select * from sys.tables.
Or SQL 2005 has a default trace, that is always ON. DIG into it to check for more information.

Maninder

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-06-26 : 11:46:10

quote:
Originally posted by maninder

check modify_date in select * from sys.tables.
Or SQL 2005 has a default trace, that is always ON. DIG into it to check for more information.
Maninder

How???

maninder
Posting Yak Master

100 Posts

Posted - 2008-06-26 : 11:52:59

Select * from ::fn_trace_getinfo(NULL)
will list the Trace file location/status/name

select * from ::fn_trace_gettable(FileName',default)
will give you the results of the trace.

in my case
select * from ::fn_trace_gettable('E:\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\log_902.trc',default)

Maninder

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-06-26 : 11:59:11

That doesn't give you date when It was changed

maninder
Posting Yak Master

100 Posts

Posted - 2008-06-26 : 12:23:33

Check the objectID,starttime,object name and relate that to TEXTData in the Trace.

Maninder

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-06-28 : 00:48:40

Sql2k5 comes standard report for that.