April 17, 2005 at 10:35 pm
hi,
I want to find the modified database objects(Tables, Proc and Fun) in last 3 days in SQL 2000.
With Regards
Praveen(DBA)
April 18, 2005 at 1:42 am
When you say modified I presume that you mean structure rather than data? That is a column has been added/deleted from a table rather than a new record added?
There doesn't seem to be an object modified date, only a creation date which is held in sysobjects in the crdate field. There is a refdate column but BOL says that it is for future use.
The way that I would do it would be to use the method described in http://www.sqlservercentral.com/columnists/jreade/howtoscheduleasqlserverdatabasecreationscript.asp to write out the database script and put it into source safe. Comparing this with the previous version would soon reveal any differences.
If you are after data changes then, unless you designed your application with datetime stamping you will probably have to use some log analyser software
April 18, 2005 at 11:41 pm
Sounds to me like you need some sort of database change management. Here's an article/white paper on the subject - http://www.innovartis.co.uk/pdf/Innovartis_An_Automated_Approach_To_Do_Change_Mgt.pdf
this white paper is the foundation for the software called DB Ghost (http://www.dbghost.com)
regards,
Mark Baekdal
+44 (0)208 241 1762
Build, Comparison and Synchronization from Source Control = Database change management for SQL Server
April 19, 2005 at 11:31 am
You might want to explore the below script (more on a create rather on an update):
Select name, xtype, crdate from dbo.sysobjects where crdate > getdate()-3 and name not like '%temp%' and name not like '#%'.
Run it in each db (using ms_foreachdb). When run the results for the first time, store the output in the perm table and when run for the next time store in temp. Compare the results.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply