减少VARCHAR长度时出错

Question

请参阅下面的DDL：

CREATE TABLE Person 
(
    ID int identity not null, 
    Name VARCHAR(100), 
    Age int, 
    EyeColour varchar(20), 
    primary key (ID)
)
CREATE INDEX Name ON Person (Name)
CREATE INDEX Age ON Person (Age)
CREATE INDEX EyeColour ON Person (EyeColour)

我可以执行以下语句：

ALTER TABLE person
ALTER COLUMN name VARCHAR(110)

但是，我无法执行以下语句：

ALTER TABLE person
ALTER COLUMN name VARCHAR(90)

错误为：

消息5074，级别16，状态1，第1行
索引"Name"依赖于列"Name"。

消息4922，级别16，状态9，第1行
由于一个或多个对象访问此列，ALTER TABLE ALTER COLUMN名称失败。

为什么我在减少VARCHAR的长度时会看到这些错误。在其他什么情况下，我会看到这个错误，例如更改数据类型？

是否有一种自动的方法来识别受数据类型更改影响的所有索引和约束并进行处理？

Answer 1

您肯定可以自动化识别部分。这里有一个脚本，它将根据表列查找所有索引和外键（我将查找检查约束留给读者练习）。由于它是一个脚本，所以在运行时必须更改参数。您也可以将其转换为存储过程。

但是，我不建议自动执行诸如删除索引或约束之类的操作。您最好查看输出，并根据您对使用这些数据的应用程序的了解来决定是否可以减少列的大小。

-- parameters
DECLARE
    @nm_schema sysname = N'Purchasing',
    @nm_table sysname = N'PurchaseOrderHeader',
    @nm_column sysname = N'EmployeeID';
DECLARE
    @id_table int,
    @id_column smallint;
SELECT @id_table = o.object_id
    FROM sys.objects o
    JOIN sys.schemas s
        ON o.schema_id = s.schema_id
    WHERE o.name = @nm_table
        AND s.name = @nm_schema
        AND o.type = 'U';
IF @@ROWCOUNT = 0
BEGIN
    RAISERROR(N'Schema %s table %s not found', 0, 1, @nm_schema, @nm_table);
    RETURN;
END;
SELECT @id_column = column_id
    FROM sys.columns
    WHERE object_id = @id_table
        AND name = @nm_column;
IF @@ROWCOUNT = 0
BEGIN
    RAISERROR(N'Column %s not found in schema %s table %s', 0, 2, @nm_column, @nm_schema, @nm_table);
    RETURN;
END;
SELECT 'Index' AS 'dependency', i.name
    FROM sys.indexes i
    JOIN sys.index_columns ic
        ON i.object_id = ic.object_id
        AND i.index_id = ic.index_id
    WHERE i.object_id = @id_table
        AND ic.column_id = @id_column
        AND NOT i.type = 0 -- heap
UNION ALL
SELECT 'FKey', f.name
    FROM sys.foreign_keys f
    JOIN sys.foreign_key_columns fc
        ON f.object_id = fc.constraint_object_id
    WHERE (f.parent_object_id = @id_table AND fc.parent_column_id = @id_column)
        OR (f.referenced_object_id = @id_table AND fc.referenced_column_id = @id_column);