Column is not dropping in Azure DW table

Question

Hi Team,

I have a table with considerable amount of data in Azure Synapse and i need to drop one column from this table. I am using the following query Alter table <TableName> Drop column <ColumnName> but am getting the following error:

Msg 5074, Level 16, State 1, Line 8
The statistics 'Stat_926ec45a53354b63b403c601944b7a38' is dependent on column 'Region'.
ALTER TABLE DROP COLUMN Region failed because one or more objects access this column.

The column Region which i am trying to drop is not used in any index.

I tried dropping the said statistics by using Drop Statistics TableName.Stat_926ec45a53354b63b403c601944b7a38

But its giving me error stating that this statistics doesn't exist.

How i can identify beforehand that which statistics needs to be deleted before deleting a column?
And how to delete this statistics?

Answer 1

I had a similar issue. The query provided earlier is not quite right. Here is the correct query:

SELECT  SCHEMA_NAME(tab.schema_id) SchemaName
      , tab.name TableName
      , col.column_id ColumnId
      , col.name ColumnName
      , s.name StatsName
      , s.auto_created AutoCreated
   , 'DROP STATISTICS ' + SCHEMA_NAME(tab.schema_id) + '.' +  tab.name + '.' +  s.name DropStatSQL
FROM    sys.tables tab
JOIN    sys.columns col ON tab.object_id = col.object_id
JOIN    sys.stats_columns sc ON col.column_id = sc.column_id
                                AND col.object_id = sc.object_id
JOIN    sys.stats s ON sc.stats_id = s.stats_id
                       AND s.object_id = sc.object_id
WHERE   tab.name = 'YourTableName'
 AND col.name = 'YourColName'

The StatsName in this query did not match the name of the stats in the error message but I tried dropping the AutoCreated statistic for the column anyway and I was then able to drop the column in the table.

Perhaps the stat name we get in the error message (in format stat_..... ) is just an alias for the real stat name.

Answer 2

@Priya Jha Thanks for using Microsoft Q&A.

You can find statistics of a column in sys.stats_columns and corresponding statistics name in sys.stats tables respectively. You can use the below query to get Statistics name against a specific column (in your case 'Region').

SELECT s.Name, OBJECT_NAME(c.object_id) as TableName, c.name as ColumnName  
FROM sys.columns c  
inner join sys.stats_columns sc  
on c.column_id = sc.column_id  
inner join sys.stats s  
on sc.stats_id = s.stats_id  
and c.name = 'Region'   

You can DROP the statistics using the name returned from above query in your code if you want to automate it but please keep in mind that deleting a statistics may affect the execution plan chosen by the query optimizer.

----------

Please do not forget to "Accept the answer" wherever the information provided helps you to help others in the community.

Answer 3

I have a similar problem. I'm trying to change the data type of a non-key field from integer to varchar, but it tells me that I can't change it because of statistics:

The statistics 'Stat_2b8171e64c644545b5147efaf4b82f9e' is dependent on column 'PROCESS_ID'.
ALTER TABLE ALTER COLUMN PROCESS_ID failed because one or more objects access this column.

When I try to drop 'Stat_2b8171e64c644545b5147efaf4b82f9e' I'm told that it doesn't exist. If I run the query you suggested against sys.stats, the statistic isn't there either. If I run DBCC show_statistics for this stat, it also tells me that it doesn't exist.

If I try to drop a statistic that does show up in the query results, it says I can't delete it because it is not a statistics collection

Please don't tell me that I need to copy the table with CTAS, drop the original, and then rename the copy, and reinstate the primary key constraint. That is a ridiculous amount of work for such a simple task, especially since I need to do it for 113 tables.