Last Modified Date

Question

Post reply

Last Modified Date

sbalaven

Ten Centuries

Points: 1127
More actions
December 27, 2007 at 12:26 am

#76857

Hi,
Is there any query available to find out the last modified date of the sql table.
I want when it was modified the table.
Balavenkatesh

Viewing 10 posts - 1 through 9 (of 9 total)

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

Minaz Amin SSChampion Points: 11052 More actions · Answer 1

There is a script available in the following link about auditing a table.

http://www.sqlservercentral.com/scripts/Maintenance+and+Management/31950/

"More Green More Oxygen !! Plant a tree today"

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 2

The script that Minaz points out is for the data... not the table schema.

There is no way to audit changes/modifications to a table's schema in SQL Server 2000 through the use of native tools.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Steve Jones - SSC Editor SSC Guru Points: 736198 More actions · Answer 3

You could set something up to look for the version in the sysobjects table, but as Jeff mentioned, there is no good way to look for when something changed.

If you had all the logs (backups), you could use something like Log Rescue (Red Gate tool, free, I work for them) to read the logs and look for schema changes to the table.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

Back on SQL 2000 I had a job that would run a checksum or checksum aggregate over some columns of the system tables to look for changes. Wasn't pretty, but it mostly worked

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

sbalaven Ten Centuries Points: 1127 More actions · Answer 5

sbalaven

Ten Centuries

Points: 1127

December 27, 2007 at 6:33 pm

#763117

Thanks for your information

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 6

PLEASE don't double post anymore... spreads resources too thin.

http://www.sqlservercentral.com/Forums/Topic436548-146-1.aspx

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Naren-274001 Ten Centuries Points: 1033 More actions · Answer 7

SELECT * from sys.procedures where modify_date >'12/20/2007'

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 8

Narendra (12/28/2007)
SELECT * from sys.procedures where modify_date >'12/20/2007'

sys.procedures doesn't exist in SQL 2000, and SQL 2000 doesn't have a modification date in sysobjects.

Both those are 2005 and higher features.