批量修改字段长度,考虑主键外键索引的情况- 王振高- 博客园
项目字段不够用,涉及的表太多,自己写的语句
/*====================================================*/
-- 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 = 'TboxName',
    @length = 50

declare @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)
)

--start
select 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 = 20

declare cursor_temp cursor for
--含该字段的表
select * from #temp
open cursor_temp
fetch  cursor_temp into @tablename,@isnullable
while @@fetch_status = 0
begin
    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
    end
end
close cursor_temp
deallocate cursor_temp

drop table #index,#temp

 

郑重声明:资讯 【批量修改字段长度,考虑主键外键索引的情况- 王振高- 博客园】由 发布,版权归原作者及其所在单位,其原创性以及文中陈述文字和内容未经(企业库qiyeku.com)证实,请读者仅作参考,并请自行核实相关内容。若本文有侵犯到您的版权, 请你提供相关证明及申请并与我们联系(qiyeku # qq.com)或【在线投诉】,我们审核后将会尽快处理。
—— 相关资讯 ——