批量修改字段长度，考虑主键外键索引的情况

【分享】批量修改字段长度，考虑主键外键索引的情况
项目字段不够用，涉及的表太多，自己写的土方法，大家有没有更简便的处理方法？

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
[解决办法]
写的很好呀,顶一下.
[解决办法]
挺复杂的。。。
[解决办法]

[解决办法]
学习！~
[解决办法]
楼主很厉害，有空教教我。
[解决办法]
还不如重建数据库，做数据移植得了
[解决办法]
这个得支持
[解决办法]
呵呵，帮顶。