批量修改欄位長度，考慮主鍵外來鍵索引的情況【轉】
-- 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
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/241379/viewspace-692773/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 父表修改與外來鍵的關係（主鍵DML與外來鍵的關係）
- oracle 修改表欄位的長度Oracle
- 如果新增欄位是外來鍵，如何處理？
- 在已存在的表結構上新增主鍵、外來鍵、聯合主鍵、聯合索引的例子索引
- SQL的主鍵和外來鍵約束SQL
- 新的主鍵和外來鍵的語法
- 查詢(看)表的主鍵、外來鍵、唯一性約束和索引索引
- 主鍵與主鍵索引的關係索引
- 如何批量使外來鍵(FK)失效
- 查詢外來鍵約束、子表欄位等資訊的SQLSQL
- DB2巧用欄位自動增長主鍵的方法DFDB2
- Oracle根據主鍵查詢外來鍵Oracle
- ORACLE: 查詢(看)表的主鍵、外來鍵、唯一性約束和索引Oracle索引
- Oracle 修改欄位型別和長度Oracle型別
- 查詢沒有索引的外來鍵索引
- Mybatis-Plus3.0預設主鍵策略導致自動生成19位長度主鍵id的坑MyBatisS3
- ORACLE MYSQL中join 欄位型別不同索引失效的情況OracleMySql型別索引
- 修改主鍵的SQLSQL
- Oracle '批量'禁用外來鍵的儲存過程Oracle儲存過程
- CSS 長度單位參考(轉)CSS
- oracle檢視當前使用者下所有外來鍵、主鍵、索引、sequence的建立語句Oracle索引
- 主鍵、自增主鍵、主鍵索引、唯一索引概念區別與效能區別索引
- 菜鳥學資料庫（四）——超鍵、候選鍵、主鍵、外來鍵資料庫
- 外來鍵缺索引檢查指令碼索引指令碼
- SAP主資料的欄位長度（ECC6.0）
- 檢查外來鍵是否有索引的指令碼索引指令碼
- 【Analytic】使用分析函式ROW_NUMBER輔助完成外來鍵的索引批量建立函式索引
- oracle中檢視一張表是否有主鍵，主鍵在哪個欄位上Oracle
- 高併發情況下 如何線上加核心表的主鍵？
- 表外來鍵未加索引之處理索引
- Oracle 外來鍵索引影響阻塞問題Oracle索引
- MySQL的主鍵和欄位型別問題總結MySql型別
- 修改外來鍵為validate時需要驗證資料是否符合外來鍵約束
- mysql的text欄位長度MySql
- 【fk_index】外來鍵中有無索引的區別Index索引
- SQL SERVER中找出拙劣的約束，索引，外來鍵SQLServer索引
- MYSQL的外來鍵MySql
- 【mybatis-plus】主鍵id生成、欄位自動填充MyBatis