引用列的统计信息是否可以防止删除该列？

本文介绍了引用列的统计信息是否可以防止删除该列？的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我正在尝试一个非常简单的放置列语句：

I'm trying a very simple drop column statement:

alter table MyTable drop column MyColumn

并收到类似的错误

消息5074，级别16，状态1，行1 统计信息'_dta_stat_1268251623_3_2'取决于列'MyColumn'。

Msg 5074, Level 16, State 1, Line 1 The statistics '_dta_stat_1268251623_3_2' is dependent on column 'MyColumn'.

最终跟随

消息4922，级别16 ，状态9，第1行 ALTER TABLE DROP COLUMN MyColumn失败，因为一个或多个对象访问此列。

Msg 4922, Level 16, State 9, Line 1 ALTER TABLE DROP COLUMN MyColumn failed because one or more objects access this column.

我认为统计数据不会阻止删除列。有吗如果是这样，由于这些显然是自动创建的统计信息，因此我不能依赖同一数据库的多个副本中的名称相同，那么如何将所有这些统计信息放在升级脚本中以在不同的数据库上执行？

I didn't think statistics prevent a column from being dropped. Do they? If so, since these are apparently auto-created statistics I can't depend on the names being the same across multiple copies of the same database, so how could I drop all such statistics in an upgrade script to be executed on a different database?

推荐答案

自动生成的统计信息，我看到它们要么具有它们代表的索引名，要么以诸如 WA_Sys _ 。

Auto-generated statistics that I have seen all either have the name of the index they represent OR start with something like WA_Sys_.

您是否100％确定这不是某人设置的一组自定义统计信息？

Are you 100% sure this is not a set of custom stats someone set up?

检查此项目：

select * FROM sys.stats WHERE name = '_dta_stat_1268251623_3_2'

...并查看 user_created 字段表示。

...and see what the user_created field indicates.

每个评论：

这未经测试，但您可以尝试以下操作：

This is untested but you could try something like:

exec sp_MSforeachdb ' use ? DECLARE @SQL varchar(max) = '''' select @SQL = @SQL + ''DROP STATISTICS '' + OBJECT_NAME(c.object_id) + ''.'' + s.name + CHAR(10) + CHAR(13) from sys.stats s INNER JOIN sys.stats_columns sc ON sc.stats_id = s.stats_id INNER JOIN sys.columns c ON c.column_id = sc.column_id WHERE c.name = ''ClaimNbr'' --and s.user_created = 1 PRINT @SQL'

更改 PRINT 转换为 EXEC （如果看起来不错）。

Change the PRINT to an EXEC if it looks good.

sp_msforeachdb 是背景中的光标，但是其余逻辑可以作为一个集合来完成。

sp_msforeachdb is a cursor in the background but the rest of the logic you can do as a set.