Rotating Header Image

List All Statistics For a Column – Update

Msg 5074, Level 16, State 1, Line 1
The statistics ‘StatName’ is dependent on column ‘ColumnName’.

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

Post to Twitter Post to Delicious Post to Digg Post to StumbleUpon

2 Comments

  1. The i-Blason website says the case is made of a “3H rating premium scratch resistant material”, but what really seems to protect it are four small feet on the case’s back corners. When you set it down screen-up, the back is raised off the surface.

  2. The company will examine the recovered phone of one of the teenagers, who went on a fishing trip but never went back.

Leave a Reply

Twitter links powered by Tweet This v1.6.1, a WordPress plugin for Twitter.