scan databases and find the table that was last used

Question

We have a sql server box right and that sql server box has many databases. NOw i have been given a task where they say that i need to scan all the databases and list all the tables that were last being accessed/used and all the tables that are not being accessed

how would i accomplish this task? is there a query which does that? is there a tool which does that?

I may have to scan various sql server boxes and scan the various databases and list all the tables which are last being accessed and also list the tables that are not being accessed at all.

Would be eagerly waiting for your replies :)

---

Thanks

Answer 1

You do need to realize that some tables may be accessed only at special periods, e.g. end of month, quarter, fiscal year, calendar year, etc.   So any analysis is (alas) still subject to questions.

However, with SQL Server 2005 and up there is a dynamic management view that shows index accesses since the server was last started. From the indexes, you can easily see which table was involved.  You will need to aggregate, keep records over time, then decide what to do.

A sample query (for a single database):

select db_name(s.database_id) dbname, object_name(s.object_id) tablename,
i.name indexname, user_seeks, user_scans, user_lookups, user_updates,
CAST((user_seeks + user_scans + user_lookups) / (user_updates * 1.0) AS DECIMAL(5,1)) Read_Upd_Ratio,
last_user_seek, last_user_scan, last_user_lookup, last_user_update
from sys.dm_db_index_usage_stats s
  join sys.indexes i on
      s.object_id = i.object_id
      AND s.index_id = i.index_id
where  user_seeks + user_scans + user_lookups < user_updates
  and user_updates > 0
order by user_seeks + user_scans + user_lookups,Read_Upd_Ratio, user_updates desc

All the best,
RLF

Answer 2

thanks a lot for that query......i did some changes to one of the table in a database A like an hour ago....and when i ran that sample query that you have given me.....i didn't come up with the database A changes in the results......do you know why?

Thanks again and would be eagerly waiting for ur reply :)

---

Thanks

Answer 3

My apologies.  The query I gave you was for finding indexes that I may want to drop.  A better version for your purposes might be:

SELECT DB_NAME(s.database_id) dbname, OBJECT_NAME(s.object_id) tablename,
i.name indexname, user_seeks, user_scans, user_lookups, user_updates,
last_user_seek, last_user_scan, last_user_lookup, last_user_update
FROM sys.dm_db_index_usage_stats s
 JOIN sys.indexes i ON 
 s.object_id = i.object_id
 AND s.index_id = i.index_id
 -- Limit to user tables
 JOIN sys.tables t ON 
 s.object_id = t.object_id
-- Any update indicates use of the table
WHERE user_updates = 0 
 -- Arbitrary number of reads, which could be system functions. 
 AND user_seeks + user_scans + user_lookups < 5 
ORDER BY user_updates, user_seeks + user_scans + user_lookups 

The first query was (because of its purpose) filtering out the row that you wanted to see.  This version should not do that. 

RLF

Answer 4

Thanks again very much for your kind response :)

but that query is not returning any results at all :)

maybe if u cud give me a rough idea about that query, i will try to make some modifications to the query. I am just a developer in .net and never wrote that kind of query before :)

thanks again for your help

---

Thanks

Answer 5

I adjusted the WHERE filter above so that it should work better. 

Remember, of course, that these numbers are only since the last time the SQL Service was started.  Any restart of the SQL Server service sets these (in memory) values back to 0.

In order to produce a clearer report, I have aggregated the data using GROUP BY so that there is only 1 row for each table, instead of 1 for each affected index.

-- Tables not in use
-- Choose how many accesses of the table to allow
-- in your result set.
DECLARE @accesscount int
SET @accesscount = 0

SELECT DB_NAME(0) AS dbname,  
    t.name AS tablename,
    SUM(user_seeks) AS sum_user_seeks, 
    SUM(user_scans) AS sum_user_scans, 
    SUM(user_lookups) AS sum_user_lookups, 
    SUM(user_updates) AS sum_user_updates,
    MAX(last_user_seek) AS last_user_seek, 
    MAX(last_user_scan) AS last_user_scan, 
    MAX(last_user_lookup) AS last_user_lookup, 
    MAX(last_user_update) AS last_user_update
-- Limit to user tables. System tables
-- are not referenced in sys.tables.
FROM sys.tables t 
-- The stats table tracks all use of a table's indexes
-- The LEFT OUTER JOINS include tables for which no
-- statistics have been captured.
 LEFT OUTER JOIN sys.dm_db_index_usage_stats s
   ON s.object_id = t.object_id
 LEFT OUTER JOIN sys.indexes i 
  ON t.object_id = i.object_id
  AND s.index_id = i.index_id
-- Any update indicates use of the table.
-- Any seeks, scans or lookups indicate table use.
-- But system functions also can scan the data. 
-- Any activity means not a candidate
-- Treat NULL values as 0 for reporting purposes
WHERE COALESCE (user_updates,0) = 0 
 AND COALESCE (user_seeks + user_scans + user_lookups,0) <= @accesscount 
 AND COALESCE(s.database_id, DB_ID()) = DB_ID()
-- Aggregate all index counts for access to a table
GROUP BY DB_NAME(s.database_id), db_name(s.database_id), t.name
---- Order the results by database and table name
ORDER BY dbname, tablename

I hope that this is more accurate and easier to understand.

Please also remember my warning that you will need to aggregate these values over time in order to get a true picture. 

RLF

Answer 6

Great on the level of help you provide for @hpjack433, code seems to requing a inner depth of batabase. I am new to it and I am required to know the change made in a database DB1, tables lately by some software.

mssql 2008, trackchange turned off, will this system at this state running on windows server 2003 OS will also show the changes in the database tables which I recently mede? i ran the query you made above, the last one. Which only shows some changes in master database. I know the database name on which the changes occour. I want to scan the database and see which table and column are effected by some changes (I know what I have entered in the fields provided by teh software, I do not know the mapping of those tables to the database table so need to figure them out) in the running software.  Do i require to change the state of the TRACK CHANGE  of the database to know it?  waiting for your response.