Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How to List All table Modified, altered, updated, inserted in sqlserver 2000 database Last N Days. Expand / Collapse
Author
Message
Posted Tuesday, October 26, 2010 3:49 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, November 26, 2010 9:38 PM
Points: 102, Visits: 53
How to List All table Modified, altered, updated, inserted in sqlserver 2000 database Last N Days.

I have two databases which is Thomasnet_N other one one Thomasnet_New

I want to find out which is recently updated which one i want to use for live testing.


Regards,

Pritam Salvi
SQL DBA
Mumbai. India[size="7"][/size]
Post #1010634
Posted Wednesday, October 27, 2010 2:46 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, November 26, 2010 9:38 PM
Points: 102, Visits: 53
I have this query to find out created table in last N days.

Select name, id, crdate
From sysobjects
Where type ='U'
and DateDiff(D,refdate,getdate()) < 7;

But it will get last created, I want last modified or insert/updated table.

Is there any idea?


Regards,

Pritam Salvi
SQL DBA
Mumbai. India[size="7"][/size]
Post #1011338
Posted Wednesday, October 27, 2010 3:36 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, May 30, 2014 6:27 PM
Points: 2,808, Visits: 7,175
you wont be able to get that information by default sorry, you can build this functionality in with triggers however this will only help for the future.

Post #1011351
Posted Wednesday, October 27, 2010 3:52 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, November 26, 2010 9:38 PM
Points: 102, Visits: 53
Thanks for reply Steveb.

Regards,

Pritam Salvi
SQL DBA
Mumbai. India[size="7"][/size]
Post #1011356
Posted Wednesday, October 27, 2010 4:10 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 2:51 PM
Points: 42,756, Visits: 35,850
You'll need a trace to get the DDL (schema) changes as SQL 2000 has no DDL triggers.


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #1011369
Posted Wednesday, October 27, 2010 4:19 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, November 26, 2010 9:38 PM
Points: 102, Visits: 53
Thanks Gail.

Regards,

Pritam Salvi
SQL DBA
Mumbai. India[size="7"][/size]
Post #1011377
Posted Thursday, September 19, 2013 10:05 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, May 6, 2014 1:46 PM
Points: 1, Visits: 38
To get the Tables Altered, you can use

SELECT name, create_date, modify_date
FROM sys.objects
WHERE modify_date >= GETDATE()-n
Post #1496481
Posted Thursday, September 19, 2013 10:35 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 2:51 PM
Points: 42,756, Visits: 35,850
davehegwood (9/19/2013)
To get the Tables Altered, you can use

SELECT name, create_date, modify_date
FROM sys.objects
WHERE modify_date >= GETDATE()-n


Won't work on SQL 2000.

3 year old thread.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #1496489
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse