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

/*====================================================*/
-- 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 = 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

 

 

 

转：http://topic.csdn.net/u/20100603/21/44500b5a-9188-4086-958a-7bb3c9d5b3a5.html