question

PriyaJha-3992 avatar image
0 Votes"
PriyaJha-3992 asked NeelTerdal-2297 published

Column is not dropping in Azure DW table

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?

azure-synapse-analytics
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

SaurabhSharma-msft avatar image
0 Votes"
SaurabhSharma-msft answered SaurabhSharma-msft edited

@PriyaJha-3992 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.

· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

@PriyaJha-3992 Please let me know if you are able to proceed further or need any help.

0 Votes 0 ·

@PriyaJha-3992 Following up to know if you are still having issues ?

0 Votes 0 ·
AnnisTim-2415 avatar image
0 Votes"
AnnisTim-2415 answered AnnisTim-2415 edited

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.








5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

iw-0618 avatar image
1 Vote"
iw-0618 answered NeelTerdal-2297 published

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.

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thanks a lot for this query. Helped me in getting rid of the stat. And the beauty is it also generates the SQL :-)

0 Votes 0 ·