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

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

阅读更多
个人分类: Ms SQL Server DataBase
所属专栏: DataBase 开发
上一篇Ms SQL中 根据表名查询字段信息,根据表名和字段判断该字段是否存在
下一篇SQL Server 2005 常见查询
想对作者说点什么? 我来说一句

没有更多推荐了,返回首页

关闭
关闭
关闭