Manoj Manohar


Hi

Any one please tell me is there any possible way to identify the table modified date.

I have checked the table created date from sysobjects or by right click properties. my requirement is to identify the exact date of table modification and column creation,alter dates.

Is there any such provision in sql server 2000 or 2005 , My application is in sql server 2000.

I need to confirm this because some database structure modification has affected my application and causing dataloss i need to check with the date of structural change of table and lost data date

can any one help





Re: Table , column created, modified date in sql server 2000 or 2005

Ajmer Dhariwal


For 2000 you need to enable auditing to capture this level of information, or have your own tracing running against that database to capture all activity that takes place against it. Something which usually only the most secure installations bother with, because of the maintenance and performance overhead.

For 2005 you have the additional option of DDL triggers, which you can implement to record (or block) any modification activity against a table (that involves using ALTER, CREATE or DROP statements). This is in addition to the traditional DML triggers.

More info at: http://msdn2.microsoft.com/en-us/library/ms190989.aspx

However, there's nothing better than implementing a security model that denies that level of access to those tables in the first place. Easier said than done, I know...






Re: Table , column created, modified date in sql server 2000 or 2005

Manoj Manohar

hi thanks for the quick reply i fear we have enabled any audit system in sql server 2000, so if no auditing system was not enbled is there anyways to find out

as of now i thing there is no way to get the information







Re: Table , column created, modified date in sql server 2000 or 2005

Ajmer Dhariwal

Unfortunately no.

For the moment is there anyway you can lockdown security so that no DDL modifications can be made to the tables in that db

That way, you can prevent this issue from recurring, and when someone does come running to you to ask for those permissions, this should give you an idea about who made the original modifications.





Re: Table , column created, modified date in sql server 2000 or 2005

Manoj Manohar

Thank you i will do as u say, Lets see if the issue re occurs




Re: Table , column created, modified date in sql server 2000 or 2005

Joe Webb

For SQL Server 2005, you can do something like:

SELECT
[name]
,create_date
,modify_date
FROM
sys.tables

But this doesn't exist in SQL Server 2000.

Joe