The postings on this site are my own and do not represent my Employer's positions, advice or strategies.

LifeAsBob - Blog



No Ads ever, except search!
Saturday, July 20, 2024 Login

Blog posts for the month of March,2021.
Unable to drop Statistics3/26/2021 10:00:29 AM
Recently ran into an issue where we were unable to drop a column statistic.

We were trying to modify a column and received the following error.

ALTER TABLE [schema].[table] ALTER COLUMN [column1] VARCHAR(MAX)

Msg 5074, Level 16, State 1, Line 1
The statistics 'Stat_52ead462d2bc49009bded731bf1417a8' is dependent on column 'column1'.
ALTER TABLE ALTER COLUMN column1 failed because one or more objects access this column.
Operation cancelled by user.

Notice the odd name of stat_{guid}, this is usually when sql server is auto generating a system name.

Searched for that statistic name and could not find it.

select *

from sys.stats so

where = 'Stat_6c8147e7477849589b585591f65b5843'

I ended up guessing that i needed to drop the statistic on the column, so ran this query to find the statistic on that column and then manually dropped the statistic.

select as statistics_name

, as column_name

, sc.stats_column_id

from sys.stats as s

inner join sys.stats_columns as sc

       on s.object_id = sc.object_id and s.stats_id = sc.stats_id

inner join sys.columns as c

       on sc.object_id = c.object_id and c.column_id = sc.column_id

where s.object_id = object_id('stg.td_click')

From Here i dropped that statistic and then was able to proceed, notice it is not named like the error, but it worked and allowed me to then alter the column.

drop statistics stg.td_click._WA_Sys_00000010_29D8AC17

Found one similar issue.

Blog Home