how to find today modified tables

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

how to find today modified tables

Author

Topic

gsrinivas.
Yak Posting Veteran

56 Posts

Posted - 2009-06-11 : 01:39:20

dear sql team,
how to find today modified tables in sql server 2000
by using sql query or procedure.

thanks
-GSrinivas

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-06-11 : 02:45:06

try this srinivas
select * from sys.objects where type ='u' and dateadd(d,datediff(d,modify_date,0),0) = dateadd(d,datediff(d,GETDATE(),0),0)

http://msdn.microsoft.com/en-us/library/aa260447(SQL.80).aspx

gsrinivas.
Yak Posting Veteran

56 Posts

Posted - 2009-06-11 : 05:11:22

thanks for your reply bklr..
but there are some errors and also not working..
hear by using sysobjects is it possible?

kindly reply.
thanks.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-11 : 05:22:59

bklr, you are still suggesting this

dateadd(d,datediff(d,modify_date,0),0) = dateadd(d,datediff(d,GETDATE(),0),0)

???

This will trash any index use over the modify_date column, so you can very much write this instead

datediff(day, modify_date, GETDATE()) = 0

Easier to read and understand.

SELECT	name
FROM	sysobjects
WHERE	Type = 'U'
	AND crdate >= DATEDIFF(DAY, 0, GETDATE())
	AND crdate < DATEDIFF(DAY, -1, GETDATE())

E 12°55'05.63"
N 56°04'39.26"

gsrinivas.
Yak Posting Veteran

56 Posts

Posted - 2009-06-11 : 05:39:05

Dear peso,
i want the updated dates and not a created date .
suppose there is table abc_tbl and this is updated on 4 days back.
now my intension is to get the updated date which is for the table 'abc_tbl'

or i can give the date as input then the tables what are the updated ones on that date.
kindly reply.

thank you very much.
-GSrinivas.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-11 : 05:44:28

There is no such information in SQL Server.
You will have to add some kind of auditing.

E 12°55'05.63"
N 56°04'39.26"

gsrinivas.
Yak Posting Veteran

56 Posts

Posted - 2009-06-11 : 09:17:29

by working with DML operations is there any recording in log files..
i think microsoft provides some technique.

NeilG
Aged Yak Warrior

530 Posts

Posted - 2009-06-11 : 09:43:23

There isn't any modified date held, this is impossible really

gsrinivas.
Yak Posting Veteran

56 Posts

Posted - 2009-06-11 : 11:21:16

hi NeilG,
modified data means (insert or update or delete) data. (DML)

by using triggers, i can get my requirement.
but without this is it not possible???

while updating tables does sql server maintain transaction logs?
in that is there any chance to store that datetime...

thanks
-srinu

gsrinivas.
Yak Posting Veteran

56 Posts

Posted - 2009-06-12 : 03:24:30

still i am waiting for solution .....
anybody please...

-srinivas

tosscrosby
Aged Yak Warrior

676 Posts

Posted - 2009-06-12 : 15:00:03

Not unless you have some type of auditing in place (see your comment on triggers). SQL does not do this for you.

Terry

-- Procrastinate now!

gsrinivas.
Yak Posting Veteran

56 Posts

Posted - 2009-06-12 : 15:24:32

ok,
if there is a solution please send an email to gsrnv@yahoo.co.in

thank you sql team
-srinivas