背景:
生产数据库有个需求,需要修改字段的小数点的保留位数,因此需要修改字段的长度
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. 重建完成之后,根据备份语句进行重建索引
本文作者为lishengyu,转载请注明。