SQL Command to determine table last used date

SQL Command to determine table last used date

Post by emily elmor » Fri, 26 Jul 2002 05:04:19



Does anyone know of a SQL Command you can issue against a
table to tell you when it was last used or updated? Tried
Sp_help but that only tells you when the table was created.
 
 
 

SQL Command to determine table last used date

Post by Allan Mitche » Fri, 26 Jul 2002 05:53:44


AFAIK you can't

There are two columns in sysobjects which sound as though they may
offer this in future releases.

Version and refdate

Allan
www.SQLDTS.com

On Wed, 24 Jul 2002 13:04:19 -0700, "emily elmore"


>Does anyone know of a SQL Command you can issue against a
>table to tell you when it was last used or updated? Tried
>Sp_help but that only tells you when the table was created.

Allan Mitchell
www.SQLDTS.com

 
 
 

SQL Command to determine table last used date

Post by BP Margoli » Fri, 26 Jul 2002 08:34:34


Emily,

As posted by Allan, SQL Server does not automatically track this
information. However, going forward, you can use SQL Server Profiler to
capture, to a table, SELECTs, INSERTs, UPDATEs and DELETEs against any or
all tables in a database, and then use your SQL skills to get the
information you've requested.

-------------------------------------------
BP Margolin
Please reply only to the newsgroups.
When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
can be cut and pasted into Query Analyzer is appreciated.


Quote:> Does anyone know of a SQL Command you can issue against a
> table to tell you when it was last used or updated? Tried
> Sp_help but that only tells you when the table was created.

 
 
 

SQL Command to determine table last used date

Post by BP Margoli » Fri, 26 Jul 2002 08:36:24


Emily,

Additionally, if you have all your transaction logs intact, you can probably
use Log Explorer from www.lumigent.com to track INSERTs, UPDATEs and DELETEs
(but not SELECTs) against tables in your database.

-------------------------------------------
BP Margolin
Please reply only to the newsgroups.
When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
can be cut and pasted into Query Analyzer is appreciated.


Quote:> Does anyone know of a SQL Command you can issue against a
> table to tell you when it was last used or updated? Tried
> Sp_help but that only tells you when the table was created.

 
 
 

1. How can I determine the date and time a table or stored procedure was last modif

How can I determine the date and time a table or stored procedure was last
modified?  Once an application has been modified in our development environment,
we need to port that app and its related tables over to production.  We I need a
way to determine which tables and stored procedures have been modified.  

Any and all help is greatly appreciated.

Anthony

2. Convert SQL 6.5 to 7.0 - Challenges

3. Needed: A nice way of determining the date and time SQL Server 7.0 was last started

4. DBCC SHRINKFILE(PRDDATA1)

5. Determine the last record in file using VACCESS active X control

6. MaxLength by TBEdit

7. Command that returns date of last modified

8. large objects

9. EXEC command to get the last backup date and time

10. How to determine the Last time a table was updated

11. Q: Getting The Date/Time Of The Last Mod Of A Sql svr Table

12. Can you determine the last time a table was updated

13. Determine last update of a Sybase table