List All Statistics For a Column – Update

Msg 4922, Level 16, State 9, Line 1

ALTER TABLE DROP COLUMN ColumnName failed because one or more objects access this column.

So what is a nice quick and easy way to list all the statistics that are dependant on that column?

My googling skills are obviously subpar this morning cause I couldn’t find anything to help me and it seems to me that this would be something that lots of people have run into. This is what I came up with:

DECLARE @SearchColumnName	nvarchar(100)
SELECT	@SearchColumnName = 'SearchColumn'
 
SELECT	OBJECT_NAME(A.object_id) as 'TableName',
		C.stats_column_id AS 'StatisticID',
		B.name as 'StatisticName',
		COL_NAME(C.object_id, C.column_id) AS [Name]
FROM	sys.tables AS A	INNER JOIN
		sys.stats B ON B.object_id = A.object_id INNER JOIN
		sys.stats_columns C ON C.stats_id = B.stats_id AND C.object_id = B.object_id
WHERE	COL_NAME(C.object_id, C.column_id) = @SearchColumnName

I dunno… seems like there should be a sp_ListAllStatisticsForAColumn system stored proc or something LOL.

If you have something easier/better please post it in a comment!!

UPDATE:
Change your WHERE Clause to this:

WHERE	COL_NAME(C.object_id, C.column_id) = @SearchColumnName AND
		B.name  NOT IN (Select ISNULL(name,'') from sys.indexes)

If you want to filter out the statistics that are created based on indexes (which can’t be dropped anyways)

Enjoy!!