Determining when statistics were last collected

Oracle:

Oracle has last_analyzed column in user_tables and user_indexes table which is accessible to schema owner. One can check for both tables and indexes when statistics was last collected.

SQL> SELECT table_name, last_analyzed
2 FROM user_tables
3 WHERE temporary = ‘N’
4 /

TABLE_NAME LAST_ANAL
—————————— ———
NEW_TEST 07-MAR-07
PADTEST 08-MAR-07
TEST_COMPUTE 02-APR-07
INPT_CITY 09-APR-07
TEST_AC 06-MAR-07

Similarly, we can also check when statistics was collected for indexes.  You can order the results whichever way you want - by last collected date or by table/index.

SQL> SELECT ui.index_name, ui.last_analyzed
2 FROM user_indexes ui
3 INNER JOIN user_tables ut ON ut.table_name = ui.table_name
4 WHERE ut.temporary = ‘N’
5 /

INDEX_NAME LAST_ANAL
—————————— ———
PK_NEW_TEST 07-MAR-07
PK_TEST_AC 06-MAR-07
PK_INPT_CITY 09-APR-07

MS SQLServer:

In SQLServer one can use nondeterministic system function STATS_DATE to see when was the last time statistics was updated for specific index.

STATS_DATE function takes two arguments table_id and index_id. Following is the SQL followed by output. You can run it from query analyzer or management studio.

SELECT t.table_name, i.name as Index_Name,
STATS_DATE(i.id, i.indid) as Statistics_last_collected
FROM information_Schema.tables t
INNER JOIN sysindexes i ON object_id(t.table_name) = i.id
WHERE t.table_type = ‘BASE TABLE’
AND i.Name is NOT NULL
AND i.Name not like ‘_WA_Sys%’
GO

Table_Name Index_Name Last_Stat_Date
———- ————- ———————–
TEST_GROUP PK_TEST_GROUP 2007-04-25 12:29:17.150
TEST_USER PK_TEST_USER 2007-04-25 12:29:17.150