Tracking DB object modifications 1

[RiyaKumar]

Hello,

Is there any way to check if any of the tables or stored procedures in the SQL Server 2000 database got modified recently.

Thanks in advance for the replies!

 

[dbomrrsm]

select *
from INFORMATION_SCHEMA.ROUTINES

this gives you a column showing when the object was last altered.

[blue]DBomrrsm[/blue]

 

[RiyaKumar]

Thanks a lot!. 'Information_schema.routines' is useful to find the modifications in stored procedures and functions. Is there any way to find the table modifications?

Thanks.

 

[hmckillop]

beat me to it again with the information_schema.

I cant find something to which holds the last altered for a table, but if the user amended it through Enterprise Manager it actually changes the created date.

i.e

select name, crdate from sysobjects where xtype = 'U'

"I'm living so far beyond my income that we may almost be said to be living apart

 

[RiyaKumar]

Thanks for the reply!
Please let me know if there is any other way to identify the tables modifications using any of the system tables.

Thanks.

 

[PROPAR]

SQL 2000 stored proc tracking is hopeless !!

1/ select name, crdate from sysobjects where xtype = 'P'
will track only creation (CREATE) and not alteration (ALTER) !

2/ "last_altered" column in "INFORMATION_SCHEMA.ROUTINES" is rotten to the core (cf.

http://support.microsoft.com/default.aspx?scid=kb;en-us;Q294350)

and even with MS workaround gives me just the same date as created column !

The SQL 6.5 old way of DROP/CREATE PROC was much more useful than SQL 2000 ALTER PROC...