项目字段不够用，涉及的表太多，自己写的土方法，大家有没有更简便的处理方法？
- SQL code
/*====================================================*/-- Author: 黄光伟-- Create date: 2010-06-03 21:00:02-- Description: 批量修改字段长度，考虑待修改字段为主键或者外键或者索引的情况 使用sp_helpindex列出索引信息 -- 版本 MSSQL2000 /*====================================================*/--参数信息declare @colname varchar(50)--字段名称declare @length int --长度declare @type varchar(20)--类型 --未考虑待完善declare @addlen int--是否有长度 --未考虑待完善--赋值select @colname = 'mat_code', @length = 50declare @tablename varchar(50),@sql varchar(8000),@exec varchar(8000)declare @pkname varchar(100)--主键名declare @pkfieldname varchar(500) --主键字段名declare @isnullable char(1) -- 是否为空declare @foreignkey varchar(100)--外键名declare @foreignname varchar(500) --外键字段名declare @displayname varchar(500) --外键对应字段名declare @displaytable varchar(50) --外键对应表名declare @display varchar(50) --外键对应字段declare @isnull char(1) -- 外键对应字段是否为空--索引临时表create table #index(index_name varchar(50),index_declare varchar(500),index_keys varchar(300))--startselect t.name,r.isnullable into #temp from sysobjects t,syscolumns r where t.id = r.id and t.xtype = 'U' and r.name = @colname --and r.length = 20declare cursor_temp cursor for--含该字段的表select * from #tempopen cursor_tempfetch cursor_temp into @tablename,@isnullablewhile @@fetch_status = 0begin begin tran --初始化 select @pkfieldname = '',@pkname = '',@foreignkey='',@foreignname='', @displayname = '',@displaytable='',@display= '' --清空索引临时表 truncate table #index --插入索引信息 insert into #index exec sp_helpindex @tablename --判断主键是否存在该字段 if exists(select 1 from #index where charindex('primary key',index_declare) > 0 and charindex(',[email protected]+',',','+replace(rtrim(ltrim(index_keys)),', ',',')+',') > 0) begin select @pkname = index_name,@pkfieldname = index_keys from #index where charindex('primary key',index_declare) > 0 and charindex(',[email protected]+',',','+replace(rtrim(ltrim(index_keys)),', ',',')+',') > 0 --删除主键 set @sql = 'alter table '+ @tablename + ' drop constraint ' + @pkname print @sql+char(13)+char(10)+'go' exec(@sql) end --重建主键另一方法 /* -- 取得主键名 select @pkname = name from sysobjects where xtype = 'PK' and parent_obj = object_id(@tablename,'U') --判断主键是否存在该字段 if exists(select 1 from sysindexkeys ,syscolumns,sysindexes where sysindexkeys.colid = syscolumns.colid and sysindexkeys.id = syscolumns.id and sysindexkeys.indid = sysindexes.indid and sysindexkeys.id = sysindexes.id and sysindexes.name = @pkname and syscolumns.name = @colname) begin -- 主键字段 select @pkfieldname = @pkfieldname+syscolumns.name+',' from sysindexkeys ,syscolumns,sysindexes where sysindexkeys.colid = syscolumns.colid and sysindexkeys.id = syscolumns.id and sysindexkeys.indid = sysindexes.indid and sysindexkeys.id = sysindexes.id and sysindexes.name = @pkname -- 刪除旧主键 set @sql = 'alter table '+ @tablename + ' drop constraint ' + @pkname print @sql+char(13)+char(10)+'go' exec(@sql) end */ --判断索引是否存在该字段 if exists(select 1 from #index where charindex('primary key',index_declare) = 0 and charindex(',[email protected]+',',','+replace(rtrim(ltrim(index_keys)),', ',',')+',') > 0) begin select @sql = '',@exec = '' select @sql = @sql + char(13)+char(10)+'drop index [email protected]+'.'+index_name+char(13)+char(10) from #index where charindex(',[email protected]+',',','+replace(rtrim(ltrim(index_keys)),', ',',')+',') > 0 and charindex('primary key',index_declare) = 0 --删除索引 print @sql+'go' exec(@sql) --索引语法 /*create unique index [ix_pln_cost_limit] on [dbo].[pln_cost_limit]([task_no], [mat_code]) on [primary]*/ select @exec = @exec+char(13)+char(10)+'create '+ case charindex('unique',index_declare) when 0 then 'index ' else 'unique index ' end + index_name+' on [email protected]+'('+replace(index_keys,'(-)',' desc ')+') on [primary]'+char(13)+char(10) from #index where charindex(',[email protected]+',',','+replace(rtrim(ltrim(index_keys)),', ',',')+',') > 0 and charindex('primary key',index_declare) = 0 end -- 取得外键名 select @foreignkey = name from sysobjects where xtype = 'F' and parent_obj = object_id(@tablename,'U') select @displaytable = name from sysobjects where xtype = 'U' and id = (select top 1 rkeyid from sysforeignkeys where constid = object_id(@foreignkey,'F')) --判断外键是否存在该字段 if exists(select 1 from sysforeignkeys t,syscolumns r,syscolumns f where t.fkeyid = r.id and t.fkey = r.colid and t.rkeyid = f.id and t.rkey = f.colid and t.constid = object_id(@foreignkey,'F') and r.name = @colname) begin -- 外键字段 select @foreignname = @foreignname+r.name+',',@displayname = @displayname + f.name+',' from sysforeignkeys t,syscolumns r,syscolumns f where t.fkeyid = r.id and t.fkey = r.colid and t.rkeyid = f.id and t.rkey = f.colid and t.constid = object_id(@foreignkey,'F') --对应字段名 select @display = f.name from sysforeignkeys t,syscolumns r,syscolumns f where t.fkeyid = r.id and t.fkey = r.colid and t.rkeyid = f.id and t.rkey = f.colid and t.constid = object_id(@foreignkey,'F') and r.name = @colname -- 刪除外键 set @sql = 'alter table '+ @tablename + ' drop constraint ' + @foreignkey print @sql+char(13)+char(10)+'go' exec(@sql) end --修改字段长度 select @sql = 'alter table ' + @tablename + ' alter column [email protected]+' varchar('+ rtrim(@length)+') ' + case @isnullable when '1' then 'null' else 'not null' end print @sql+char(13)+char(10)+'go' exec(@sql) -- 创建主鍵 if isnull(@pkfieldname,'') <> '' begin set @sql = 'alter table ' + @tablename + ' add constraint ' + @pkname + ' primary key clustered(' + @pkfieldname + ') on [primary]' print @sql+char(13)+char(10)+'go' exec(@sql) end --重建索引 if isnull(@exec,'') <> '' begin print @exec+'go' exec(@exec) select @exec = '' end -- 创建外鍵 /* 创建语法 ALTER TABLE [dbo].[wrkshop_check] ADD CONSTRAINT [wrk_mat_code] FOREIGN KEY ( [mat_code] ) REFERENCES [MAT_MASTER] ( [MAT_CODE] ) */ if @foreignname <> '' begin --构建外键字段长度需一致 --修改外键对应表的字段长度 --是否为空 select @isnull = isnullable from syscolumns where id = object_id(@displaytable,'U') and name = @display --修改长度 select @sql = 'alter table ' + @displaytable + ' alter column [email protected]+' varchar('+ rtrim(@length)+') ' + case @isnull when '1' then 'null' else 'not null' end print @sql+char(13)+char(10)+'go' exec(@sql) delete from #temp where name = @displaytable --重建外键 select @foreignname = left(@foreignname,len(@foreignname) - 1), @displayname = left(@displayname,len(@displayname) - 1) set @sql = 'alter table ' + @tablename + ' add constraint ' + @foreignkey + ' foreign key (' + @foreignname + ') REFERENCES ' + @displaytable + '([email protected]+')' print @sql+char(13)+char(10)+'go' exec(@sql) end if @@error > 0 begin rollback tran close cursor_temp deallocate cursor_temp drop table #index return end else begin print '-----------------------------' commit tran fetch next from cursor_temp into @tablename,@isnullable endendclose cursor_tempdeallocate cursor_tempdrop table #index,#temp