Errors when decreasing VARCHAR length

Question

See below 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)

I can fulfill the following statement:

ALTER TABLE person
ALTER COLUMN name VARCHAR(110)

However, I cannot fulfill the following statement:

ALTER TABLE person
ALTER COLUMN name VARCHAR(90)

Mistake:

Msg 5074, Level 16, State 1, Line 1
The index "Name" depends on the name of the column.

Msg 4922, level 16, state 9, line 1
ALTER TABLE ALTER COLUMN name failed because one or more objects are accessing this column.

Why do I see these errors when I reduce the length VARCHAR. In what other scenarios will I see this error, for example. change data type?

Is there an automated way to determine all the indices and restrictions associated with changing the data type and processing it?

Answer 1

. script, ( ). script, . .

, . , , , .

-- 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);