if exists(select 1 from sysobjects tbl with (nolock), syscolumns col with (nolock) where tbl.xtype = 'U' and tbl.id = col.id and col.name = 'code_key' and length < 20 and tbl.name = 'sygencode')
begin
declare @name varchar(256)
set @name = ''
select @name = tbl.name from sysobjects tbl with (nolock) where tbl.parent_obj
in ( select id from sysobjects with (nolock) where name ='sygencode') and
xtype ='PK'
if @name <> ''
begin
exec('ALTER TABLE sygencode DROP CONSTRAINT ' + @name)
if exists(select 1 from sysindexes a with (nolock) where a.name = @name)
exec('DROP INDEX sygencode.' + @name)
end
if exists(select 1 from sysindexes b with (nolock)
where b.name = 'sygencode_index')
exec('DROP INDEX sygencode.sygencode_index')
exec('ALTER TABLE sygencode ALTER COLUMN code_key varchar(20) not null')
if @name <> ''
begin
exec('ALTER TABLE sygencode ADD CONSTRAINT ' + @name +
' PRIMARY KEY (code_name, code_key, com_code)')
if not exists (select 1 from sysindexes a with (nolock)
where a.name = @name)
exec('CREATE INDEX ' + @name + ' ON sygencode (code_name,
code_key, com_code)')
end
if not exists(select 1 from sysindexes b with (nolock)
where b.name = 'sygencode_index')
exec('CREATE INDEX sygencode_index ON sygencode (code_name, code_key)')
end
目的: sygencode表中有一个field code_key, 原来是 char(10) 的, 现在发现不够用, 所以想改为 varchar(20)
问题: 表中有四个字段 code_name, code_key, next_code, com_code 其中一个主键约束是在(code_name, code_key, com_code), 两个索引(因为历史原因, 有些数据库只加了一个索引)其中一个为PK的索引, 另一个为sygencode_index, 因为加了约束和索引, 所以不可以直接alter column
解决: 在alter column前先将约束/索引删除, 将其名称保留, 在alter table后补回缺点: 必须知道原先的索引/主键为哪几个字段
P.S.: 使用 sp_help,sp_helpindex 查看索引, 表结构