Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I'm in the planning stage/phase of migrating one of our SQL Server 2000 databases to 2008. Our database holds numerous tables (hundreds) and stored procedures (hundreds). I'm trying to figure out which are in use and which are not. My first guess was to look in the sysobjects table and see if there's a field that stores the a 'last accessed' but I can't seem to find anything within the MSDN documentation.

What's the best way to go about investigating which objects are in use or not in SQL Server 2000?

share|improve this question
add comment

2 Answers

up vote 1 down vote accepted

Out of luck. No way. SQL Server does not track usage timestamps for objects.

share|improve this answer
    
I can't verify this, but all signs point to your answer. Good enough for me. –  osij2is Mar 16 '11 at 19:20
add comment

USE YourDatabaseHere;

GO

SELECT name, create_date, modify_date

FROM sys.objects

GO

I think this is what you are looking for?

EDIT:

http://www.sqlteam.com/article/using-ddl-triggers-in-sql-server-2005-to-capture-schema-changes - a DDL way which may better suit the requirement....

share|improve this answer
    
Not quite. I'm looking for a way to see when a stored procedure or table was accessed last. In other words, the create_date and modify_date are for changes made to the objects. I want to know if there's a way to see when the object was being used (whether by exec call or sql query). –  osij2is Oct 11 '10 at 15:15
    
sqlteam.com/article/… maybe what you're looking for then? –  Khushil Oct 18 '10 at 18:15
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.