The answer was already given on the SQLTeam site by Saggi Neumann:
Since the DMV sys.dm_db_index_usage_stats also tracks changes in indexes (not only seeks and scans) in the user_updates column, it might list some indexes that aren't used for querying but are updated. Your query would just get you indexes on tables that are not touched
To summarize: the query with the large results reports indexes which are not used, because the related table was not used (so far). Bad assumption, especially if you run this script directly after a SLQ restart 🙂
My script (and the suggested modification from Saggi) reports indexes which are not optimal or poor used (hits compared to updates); the table itselves is being used, but the related indexes are not optimal.
Note: unless you have a very strict naming convention, I would not rely on the 'PK' exclusion of indexes. instead query the column [is_primary_key] on sys.indexes
Wilfred
The best things in life are the simple things