引用列的统计信息是否会阻止删除该列？

发布于 2024-12-12 14:11:24 字数 487 浏览 0 评论 0原文

我正在尝试一个非常简单的 drop column 语句：

alter table MyTable drop column MyColumn

并收到几个错误

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

最终紧随其后的是

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

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

原文

I'm trying a very simple drop column statement:

alter table MyTable drop column MyColumn

and receiving several errors along the lines of

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

followed ultimately by

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?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

空城之時有危險 2024-12-19 14:11:24

JNK 答案中提出的代码不起作用，但这个想法很好。如果您想删除所有用户创建的统计信息，这是我经过测试的解决方案：

DECLARE @sql NVARCHAR(MAX)

DECLARE statCursor CURSOR FOR 
SELECT 
    'DROP STATISTICS ' + QUOTENAME(SCHEMA_NAME(t.schema_id)) 
                        + '.' + QUOTENAME(t.name) 
                        + '.' + QUOTENAME(st.name) AS sql
FROM
    sys.stats AS st 
    INNER JOIN sys.tables AS t
        ON st.object_id = t.object_id
WHERE
    st.user_created = 1
ORDER BY 1;

OPEN statCursor;

FETCH NEXT FROM statCursor INTO @sql
WHILE @@FETCH_STATUS = 0  
BEGIN  
    PRINT @sql
    EXEC sp_executesql @sql
    FETCH NEXT FROM statCursor INTO @sql
END  
CLOSE statCursor  
DEALLOCATE statCursor

The code proposed in JNK answer does not work, but the idea is good. If you want to delete all user created statistics this my tested solution :

DECLARE @sql NVARCHAR(MAX)

DECLARE statCursor CURSOR FOR 
SELECT 
    'DROP STATISTICS ' + QUOTENAME(SCHEMA_NAME(t.schema_id)) 
                        + '.' + QUOTENAME(t.name) 
                        + '.' + QUOTENAME(st.name) AS sql
FROM
    sys.stats AS st 
    INNER JOIN sys.tables AS t
        ON st.object_id = t.object_id
WHERE
    st.user_created = 1
ORDER BY 1;

OPEN statCursor;

FETCH NEXT FROM statCursor INTO @sql
WHILE @@FETCH_STATUS = 0  
BEGIN  
    PRINT @sql
    EXEC sp_executesql @sql
    FETCH NEXT FROM statCursor INTO @sql
END  
CLOSE statCursor  
DEALLOCATE statCursor

回复收藏 0 原文

清风夜微凉 2024-12-19 14:11:24

我见过的自动生成的统计信息要么具有它们所代表的索引的名称，要么以诸如 WA_Sys_ 之类的内容开头。

您是否 100% 确定这不是某人设置的一组自定义统计数据？

检查一下：

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

...并查看 user_created 字段指示的内容。

每条评论：

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

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。

sp_msforeachdb 是一个在后台的游标，但其余的逻辑您可以作为一个集合来执行。

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

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

Check this:

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

...and see what the user_created field indicates.

Per comment:

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'

Change the PRINT to an EXEC if it looks good.

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

回复收藏 0 原文

~没有更多了~