When I got back to class this morning, I showed the class this blog, and the solution I had written to the question from yesterday. It was only then that I found out the need was for SQL Server 2000.

Where SQL 2005 has the catalog view sys.stats with a very queryable column called no_recompute, in SQL 2000, the information on whether autostats is enabled for a table or index is embedded as a bit in the status field of sysindexes. By examining the definition of the sp_autostats procedure, I was able to determine that if the 25th bit was set, it indicated that autostats was turned off for the particular index. I borrowed a few lines of code from sp_autostats and created the following view, which will report the automatic statistics setting and the last statistics update date on all tables and indexes in a database:

CREATE VIEW Stats_Info AS
  SELECT 
    quotename(object_name(si.id),'[') AS Table_Name,
     quotename(si.name, '[') AS Index_Name,
     CASE (si.status & 16777216)
             WHEN 16777216
                 THEN 'OFF'
                 ELSE 'ON'
             END AS Auto_Stats,
     stats_date(id,si.indid) As Last_Update_Date
  FROM sysindexes si
  WHERE id > 100 AND
        si.indid BETWEEN 1 AND 254

If you just want to see the indexes that have autostats turned off, you can query the view:

  SELECT * FROM Stats_Info
  WHERE Auto_Stats = 'OFF'

Have fun!

~Kalen