记录sqlserver因字段被索引包括导致修改字段失败问题

lishengyu 366 0

背景:

生产数据库有个需求,需要修改字段的小数点的保留位数,因此需要修改字段的长度

    alter table xxx alter column xxxx decimal(10,4);
但是实际跑的时候,发现如下报错:
[Err] 42000 - [SQL Server]The index 'query_batch_cdis_file_repay' is dependent on column 'service_rate'.
42000 - [SQL Server]ALTER TABLE ALTER COLUMN service_rate failed because one or more objects access this column.
提示索引: query_batch_cdis_file_repay 有包括该列
这时候去数据库里面查看该索引:
#查看表索引,但不包括包含列

exec sp_helpindex tablename;
查看包含列有两种方法:

1. 用工具查看(navicat直接查看表属性)

2. 这里提供一个网上的存储过程

    SELECT
QUOTENAME(SCHEMA_NAME(t.schema_id)) AS [架构名称],
QUOTENAME(t.name) AS [数据表名称],
QUOTENAME(i.name) AS [索引名称],
i.type_desc as [索引类型],
i.is_primary_key as [是否主键],
i.is_unique as [是否唯一],
i.is_unique_constraint as [是否外键],
STUFF(REPLACE(REPLACE((
SELECT QUOTENAME(c.name) + CASE WHEN ic.is_descending_key = 1 THEN ' DESC' ELSE '' END AS [data()] 
FROM sys.index_columns AS ic
INNER JOIN sys.columns AS c ON ic.object_id = c.object_id AND ic.column_id = c.column_id
WHERE ic.object_id = i.object_id AND ic.index_id = i.index_id AND ic.is_included_column = 0
ORDER BY ic.key_ordinal
FOR XML PATH
), '<row>', ', '), '</row>', ''), 1, 2, '') AS [索引键列表],
STUFF(REPLACE(REPLACE((
SELECT QUOTENAME(c.name) AS [data()]
FROM sys.index_columns AS ic
INNER JOIN sys.columns AS c ON ic.object_id = c.object_id AND ic.column_id = c.column_id
WHERE ic.object_id = i.object_id AND ic.index_id = i.index_id AND ic.is_included_column = 1
ORDER BY ic.index_column_id
FOR XML PATH
), '<row>', ', '), '</row>', ''), 1, 2, '') AS [包含列信息],
u.user_seeks,
u.user_scans,
u.user_lookups,
u.user_updates
FROM sys.tables AS t
INNER JOIN sys.indexes AS i ON t.object_id = i.object_id
LEFT JOIN sys.dm_db_index_usage_stats AS u ON i.object_id = u.object_id AND i.index_id = u.index_id
WHERE t.is_ms_shipped = 0
AND i.type <> 0

3. 解决过程

1. 找到了相关的索引之后,进行索引备份,然后删除该索引
2. 删除索引之后,进行修改字段
3. 重建完成之后,根据备份语句进行重建索引

发表评论 取消回复
表情 图片 链接 代码

分享