User created index question - sp_Blitz 5/17/18 - Incorrect output?

Question

I have SQL Server 2014 CU11 with sp_Blitz 5/17/18 8:44AM release. sp_Blitz is reporting that I have user created statistics on some of my databases. I've executed a script that deletes all user created statistics. However, when I re-run sp_Blitz it still reports them. If I use the T-SQL on Brent's page: SELECT * FROM sys.stats WHERE user_created = 1 then I see all the user created indexes. However if I use alternate code like:

SELECT st.name AS TableName, ss.name AS StatisticName
FROM sys.stats ss
JOIN sys.tables st ON ss.object_id=st.object_id
WHERE ss.user_created = 1
ORDER BY st.name, ss.name;

I see no user created indexes. I've tried several variations of the "find user stats" script & the only one that reports any is Brent's. Why would this be? I've removed almost all the user created statistics, and would like to take care of these leftovers if possible.

Answer 1

You can have rows in sys.stats without a corresponding row in sys.tables.

Classic example: an indexed view. Watch this:

CREATE TABLE dbo.T1 (ID INT PRIMARY KEY CLUSTERED);
INSERT INTO dbo.T1 VALUES (1);
GO
CREATE OR ALTER VIEW dbo.vwJetta WITH SCHEMABINDING AS
  SELECT T1.ID
  FROM dbo.T1;
CREATE UNIQUE CLUSTERED INDEX CLIX ON dbo.vwJetta(ID);
GO
CREATE STATISTICS STAT ON dbo.vwJetta(ID);
GO
SELECT * FROM sys.stats WHERE user_created = 1;

Our query correctly shows a row:

Whereas your query does not, because there's no table involved:

Moral of the story: never, ever doubt anything you see on BrentOzar.com. Just kidding. But believe what you're seeing right here, and get to work digging into those stats.