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