How do I find indexes that have statistics_norecompute = ON

The column is no_recompute in sys.stats which says

Every index will have a corresponding statistics row with the same name and ID (sys.indexes.object_id = sys.stats.object_id AND sys.indexes.index_id = sys.stats.stats_id), but not every statistics row has a corresponding index.

So a JOIN between sys.indexes and sys.stats will match the index for you

Reason:

• statistics can be for columns or indexes
• an index has exactly one statistic.
• STATISTICS_NORECOMPUTE applies to the statistic for that index, not the index itself

You can use this query:

select TableName = so.name, 
       IndexName = si.name, 
       StatName = s.name, 
       s.no_recompute
  from sys.indexes si
           inner join sys.stats s on si.object_id = s.object_id
           inner join sys.objects so on si.object_id = so.object_id
  where     no_recompute = 0
        and so.[type] in ('U', 'V')
order by so.name, si.name, s.name