sql - 减少 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)

错误是:

Msg 5074, Level 16, State 1, Line 1
The index 'Name' is dependent on column 'name'.

Msg 4922, Level 16, State 9, Line 1
ALTER TABLE ALTER COLUMN name failed because one or more objects access this 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);