sql - Errors when reducing VARCHAR length -

please see ddl below:

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 execute following statement:

alter table person alter column name varchar(110) 

however, cannot execute following statement:

alter table person alter column name varchar(90) 

the error is:

msg 5074, level 16, state 1, line 1
index 'name' dependent on column 'name'.

msg 4922, level 16, state 9, line 1
alter table alter column name failed because 1 or more objects access column.

why seeing these errors when reduce length of varchar. in other scenarios see error e.g. change data type?

is there automated way of identifying indexes , constraints affected data type change , dealing them?

you automate identification part. here script find indices , foreign keys depending on table column (i have left finding check constraints exercise reader). script, have change parameters when run it. turn stored procedure.

however, don't recommend automating actions such dropping indices or constraints. better off reviewing output , deciding if ok reduce sizes of columns or not in light of know applications use these data.

-- 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     sys.objects o     join sys.schemas s         on o.schema_id = s.schema_id     o.name = @nm_table         , s.name = @nm_schema         , 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     sys.columns     object_id = @id_table         , 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' 'dependency', i.name     sys.indexes     join sys.index_columns ic         on i.object_id = ic.object_id         , i.index_id = ic.index_id     i.object_id = @id_table         , ic.column_id = @id_column         , not i.type = 0 -- heap union select 'fkey', f.name     sys.foreign_keys f     join sys.foreign_key_columns fc         on f.object_id = fc.constraint_object_id     (f.parent_object_id = @id_table , fc.parent_column_id = @id_column)         or (f.referenced_object_id = @id_table , fc.referenced_column_id = @id_column);