Column is not dropping in Azure DW table

Priya Jha 871 Reputation points
2020-12-03T11:24:48.373+00:00

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
Azure Synapse Analytics
An Azure analytics service that brings together data integration, enterprise data warehousing, and big data analytics. Previously known as Azure SQL Data Warehouse.
4,671 questions
0 comments No comments
{count} votes

3 answers

Sort by: Most helpful
  1. iw 101 Reputation points
    2021-06-29T06:02:50.513+00:00

    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 person found this answer helpful.

  2. Saurabh Sharma 23,791 Reputation points Microsoft Employee
    2020-12-03T13:10:06.99+00:00

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


  3. Annis, Tim 6 Reputation points
    2021-04-13T19:09:02.033+00:00

    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.

    0 comments No comments