项目字段不够用，涉及的表太多,自己写的语句
/*====================================================*/
-- 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
