Alter Table - Find who altered it and when

Question

Alter Table - Find who altered it and when

Santhoshkumar KB

SSCertifiable

Points: 5066
More actions
July 7, 2010 at 6:03 am

#387287

Hi,
To know the last datetime when the table altered, we use
select name,create_date,modify_date from sys.tables
where name = 'myTable'
Is there a way to find who altered the table?
will it be recorded in any log(like transaction log) or somewhere else?
Thanks,
KB
Thanks,
Santhosh
Human Knowledge Belongs To The World !!

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323482 More actions · Answer 1

whodunnit info is contained in the Default Trace, which keeps only a brief history of all DDL changes(CREATE/ALTER/DROP...not SELECT UPDATE DELETE, which is DML statements.

only a limited amount of data is kept before the log is rolled over for reuse, so if a lot of time has gone by, the data might not be there any more.

easiest way is to use the built in GUI report from SSMS, which is filtered byt he database you right click to get the report from:

:

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Santhoshkumar KB SSCertifiable Points: 5066 More actions · Answer 2

Thank you.

But it contains only 15mins of information...

that too all the information are related to index like

IX_indexName:

DDL Operation: ALTER

Time:

Login Name:

User Name:

Is there any way to find atleast 2days old log?

Thanks,

KB

Thanks,
Santhosh

Human Knowledge Belongs To The World !!

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 3

If the record of the change has already been dropped from the default trace (which only contains 5 files of 20MB each), no.

If you wish to track this kind of thing, you need either a custom server-side trace or a DDL trigger with your own custom logging table.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Santhoshkumar KB SSCertifiable Points: 5066 More actions · Answer 4

GilaMonster (7/7/2010)
If the record of the change has already been dropped from the default trace (which only contains 5 files of 20MB each), no.
If you wish to track this kind of thing, you need either a custom server-side trace or a DDL trigger with your own custom logging table.

Thank You.

-

KB

Thanks,
Santhosh

Human Knowledge Belongs To The World !!

Atif-ullah Sheikh SSChampion Points: 12495 More actions · Answer 5

If there is no info in default trace, than there is hardly any way to get the required info.

Apply DDL trigger for future.

----------------------------------------------------------------------------------------------------------------------------------------------------------------------

Sometimes, winning is not an issue but trying.
You can check my BLOG [font="Arial Black"]here[/font][/url][/right]