【分享】批量修改字段长度，考虑主键外键索引的情况
项目字段不够用，涉及的表太多，自己写的土方法，大家有没有更简便的处理方法？
- 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(','+@colname+',',','+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(','+@colname+',',','+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(','+@colname+',',','+replace(rtrim(ltrim(index_keys)),', ',',')+',') > 0) begin select @sql = '',@exec = '' select @sql = @sql + char(13)+char(10)+'drop index dbo.'+@tablename+'.'+index_name+char(13)+char(10) from #index where charindex(','+@colname+',',','+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 '+@tablename+'('+replace(index_keys,'(-)',' desc ')+') on [primary]'+char(13)+char(10) from #index where charindex(','+@colname+',',','+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 '+@colname+' 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 '+@display+' 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 + '('+@displayname+')' 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
[解决办法]
强大 学习
[解决办法]
长痛不如短痛，把数据库改好为重
[解决办法]
先顶。
[解决办法]
- SQL code
帮公司开发人事考勤软件时工号是四位，现在快用完了，正头痛升位的问题呢，正好有帮助。不过存储过程、函数、报表里涉及到这些字段的要全部改到可能就有点麻烦了
[解决办法]
学习，接分
[解决办法]
很强大，很麻烦。
[解决办法]
lou shang !
[解决办法]
ddddddddddddd
[解决办法]
以后赚分就靠它了
抓紧收藏.
[解决办法]
dfsafasdjakfbhasjklfajslhndajkslfdsja
[解决办法]
mark up 关注
[解决办法]
shou
[解决办法]
学习 学习
[解决办法]
学习 mack JF
[解决办法]
asdf upupupupu!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
[解决办法]
汗啊。。。
[解决办法]
up 。。。
[解决办法]
ddddddddddddddd
[解决办法]
TTTTTTTTTTT
[解决办法]
[解决办法]
[解决办法]
[解决办法]
学习中……
[解决办法]
轻轻地,, 我走了.. 正如我轻轻地来~
[解决办法]
如果进行批量、智能修改，无疑还是脚本来得强大而高效~！
这是自动化最好的方法，代码是最好的工具~
[解决办法]
[解决办法]
mark 一下
[解决办法]
这个东西 还不是很清楚
[解决办法]
学习，学习[img=http://forum.csdn.net/PointForum/ui/scripts/csdn/Plugin/001/face/0.gif][/img]
[解决办法]
10分可用分！小技巧：教您如何更快获得可用分
[解决办法]
学习了，不错
[解决办法]
牛，学习
------解决方案--------------------
真是不错，谢谢，学习咯！！
[解决办法]
学习了
[解决办法]
这么大批量的数据库结构修改，真的很强大。
[解决办法]
关注本贴
[解决办法]
由于基础有限，先MARK一下了。
[解决办法]
学习 学习
[解决办法]
我一般遇到这种情况都是在PD上改，然后重新导出来，象这种情况可以建立一个域，然后可以实现批量修改。
[解决办法]
接分走人，哇卡卡
[解决办法]
[解决办法]
顶一下。。
[解决办法]
very good,OK
[解决办法]
学习了，，，，，，，，，，，，，，
[解决办法]
学习了
[解决办法]
关注一下
[解决办法]
关注并学习，
[解决办法]
功能很强，值得学习。
[解决办法]
改数据库结构是件痛苦的事情
[解决办法]
长痛不如短痛，把数据库改好为重
[解决办法]
[解决办法]
kanbudong
[解决办法]
】批量修改字段长度，考虑主键外键索引的情况
[解决办法]
if exists(select name from sysobjects where name='usp_AlterTable_FieldWidth')
drop procedure usp_AlterTable_FieldWidth
go
create procedure usp_AlterTable_FieldWidth (@colName varchar(100),@colWidth int,@NotDeal varchar(1000),@Precision int=-1)
as
begin
declare @tblName varchar(100),
@strSql varchar(8000),
@PK varchar(500)
declare cur_GetName cursor for
select b.name from syscolumns a,sysobjects b where a.name=@colName and a.id=b.id and b.type='u'
open cur_GetName
fetch next from cur_GetName into @tblName
while @@Fetch_Status=0
begin
select @PK=''
if charindex(@tblName,@NotDeal)=0
begin
select @PK=dbo.Fn_GetPKField(@tblName,@ColName)
if @PK<>''
begin
select @strSql='alter table '+@tblName+' drop constraint '+@PK
exec(@strSql)
end
if @Precision=-1
select @strSql='alter table '+@tblName+' alter column '+@colName+' varchar('+
ltrim(rtrim(convert(varchar(8),@colWidth)))+')'
else
select @strSql='alter table '+@tblName+' alter column '+@colName+' numeric('+
ltrim(rtrim(convert(varchar(8),@colWidth)))+','+
ltrim(rtrim(convert(varchar(8),@Precision)))+')'
if @PK<>''
select @strSql=@strSql+' not null '
exec(@strSql)
if @PK<>''
begin
select @strSql='ALTER TABLE [dbo].['+@tblName+'] WITH NOCHECK ADD '+
'CONSTRAINT [PK_'+@tblName+'] PRIMARY KEY CLUSTERED '+
'(['+@colName+']) ON [PRIMARY]'
exec(@strSql)
end
end
fetch next from cur_GetName into @tblName
end
close cur_GetName
deallocate cur_GetName
end
go
[解决办法]
回帖 拿分 学习 收藏
[解决办法]
good
[解决办法]
写的很好呀,顶一下.
[解决办法]
挺复杂的。。。
[解决办法]
[解决办法]
学习！~
[解决办法]
楼主很厉害，有空教教我。
[解决办法]
还不如重建数据库，做数据移植得了
[解决办法]
这个得支持
[解决办法]
呵呵，帮顶。