In class today, we were talking about automatic updating of statistics, and that you could use sp_autostats to turn off the automatic updating for just a single table, which is vastly preferable to turning it off for the entire database.
One of the students asked how she could determine which tables had the the automatic statistics feature turned off. She said she had an SAP database with tens of thousands of tables, and they had followed SAP's instructions to set no_recompute on some of the tables, but now she couldn't remember which ones.
So I was all set to have some fun analyzing the code in sp_autostats to see how it set the option on, and use that as a basis for writing a script that I was imagining would have to join at least 3 different metadata objects to get the necessary information.
But I was very disappointed to discover that everything I need is in sys.stats. A very simple query retrieved the table names and index names from this view, and a CASE expression turned the 0 or 1 in the no_recompute column into a YES or a NO, indicating whether the index had its statistics automatically updated or not.
To make the script a little fancier I put the original SELECT into a FROM clause to make it a derived table, and then selected just the indexes that didn't have automatic statistics updates enabled.
SELECT Table_Name, Index_Name
FROM
(
SELECT object_name(object_id) as Table_Name,
name as Index_Name,
CASE no_recompute
WHEN 0 THEN 'Yes'
ELSE 'No'
END as Auto_Update
FROM sys.stats
WHERE object_id > 100
AND object_name(object_id) not like 'queue%'
AND auto_created = 0) AS autostats
WHERE Auto_Update = 'No'
If you want to see the list of all the indexes and whether or not statistics are auto updated, you can just run the SELECT inside the derived table definition:
SELECT object_name(object_id) as Table_Name,
name as Index_Name,
CASE no_recompute
WHEN 0 THEN 'Yes'
ELSE 'No'
END as Auto_Update
FROM sys.stats
WHERE object_id > 100
AND object_name(object_id) not like 'queue%'
AND auto_created = 0
Have fun...
~Kalen