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!!
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)