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