SQL Table Modified Date

[oumahexi]

Hi

I'd like to identify when a table / view was last updated. My thinking is down the lines of windows explorer, where I could see for instance:

Name Owner type Create Date Modified Date

Is that possible? Or are there any other suggestions as to how I can tell if a table or view is being updated regularly?

My system has a series of stored procedures which are supposed to run updates and I'd like to confirm that this is actually working, and when it last worked.

I've searched the web for information on this but to no avail, I'm thinking that it can't be done, but who knows, maybe there's another way...

Many thanks in advanced

 

[SQL_Hell]

When you say table was last updated do you mean the table design wwas changed? or data was changed?

 

[oumahexi]

When you say table was last updated do you mean the table design wwas changed? or data was changed?

Both would be useful, but right at the moment my main concern is when the data was changed, this would let me see if the sps had run.

 

[SQL_Hell]

Ok.. easiest way is just to add a column in your table called something like ModifiedDate then for every update or insert then fill that column with getdate().

If you cannot change all your insert or update procedures then we can start looking at triggers.

 

[oumahexi]

Thanks SQL, I'll try that, in a few weeks, when I get rid of this backlog... I just thought there might be a quick way to see it so I could sign off on a piece of work quickly.

Your help, as always, is very much appreciated.

 

[SQL_Hell]

Hey again,

Just for completeness....
Its common practice to have the following audit columns in your table:

CreatedDate (default getdate()) - date the record was created
CreatedUser (default system_user) - User that created the record
Modifieddate - Date record was modified
Modifieduser - User that modified the data.

I recommend you put this logic into any of the tables you want to create audits for, it will pay dividends at somepoint trust me

 

[oumahexi]

I do. It seems logical that you should have some sort of audit trail. Obviously whoever set the SQL up didn't know about this, and I certainly didn't.

You'd have thought, wouldn't you, that SQL, being a Microsoft product would have these areas as standard options on the Enterprise database view.

Thank you so much.

 

[oumahexi]

Hey again,

Just for completeness....
Its common practice to have the following audit columns in your table:

CreatedDate (default getdate()) - date the record was created
CreatedUser (default system_user) - User that created the record
Modifieddate - Date record was modified
Modifieduser - User that modified the data.

I recommend you put this logic into any of the tables you want to create audits for, it will pay dividends at somepoint trust me

Do I need special permissions to do this? The "add" button is greyed out when I select add/remove columns. There are only the four default options available.

Or do I have to create them in SQL Analyser?

This is all new and exciting to me. Things they never taught us on our training courses...

 

[SQL_Hell]

Is this sql2005 or sql2000?

But anyway your login needs to be a member of the "database_owner" role, or if you're not allowed this then ask to be a member of "ddl_admin" role.

 

[Marc Wentink]

I hope this helps, it took me a while to find out myself, if you are looking for the same type of info, try this:

select name, modify_date from sys.tables order by modify_date desc