sqlserver 游标

DECLARE ChangeInvCodeCursor CURSOR
FOR SELECT A.name AS tablecolumn,C.name AS tablename FROM sys.columns A LEFT JOIN sys.types B ON A.user_type_id = B.user_type_id RIGHT JOIN SYS.tables C ON C.object_id=A.object_id WHERE A.name ='CINVCODE'OR A.name='INVCODE' ORDER BY C.NAME
--打开游标
OPEN ChangeInvCodeCursor
DECLARE @tablecolumn1 nvarchar(120)
DECLARE @tablename1 nvarchar(120)
DECLARE @strsql nvarchar(2048)
DECLARE @restrain nvarchar(2048)
DECLARE @BeforeCode nvarchar(120)
DECLARE @afterCode nvarchar(120)
FETCH NEXT FROM ChangeInvCodeCursor INTO @tablecolumn1,@tablename1
WHILE @@FETCH_STATUS =0
BEGIN
--变化前存货编码
set @BeforeCode='1111111111'
--变化后存货编码
set @afterCode='05010501035'
set @restrain=''
--更新语句
set @strsql='update '+@tablename1+' set '+@tablecolumn1+'='''+@afterCode+''' where '+@tablecolumn1+'='''+@BeforeCode+''''
--判断是否有外键约束，如果有禁用
select @restrain='ALTER TABLE [' + b.name + '] NOCHECK CONSTRAINT ' + a.name +';' from sysobjects a ,sysobjects b where a.xtype ='f' and a.parent_obj = b.id and b.name=''+@tablename1+''
if @restrain!=''
begin
exec(@restrain)
end
--执行变更
exec(@strsql)
--启用约束
set @restrain=''
select @restrain ='ALTER TABLE [' + b.name + '] CHECK CONSTRAINT ' + a.name +';' from sysobjects a ,sysobjects b where a.xtype ='f' and a.parent_obj = b.id and b.name=''+@tablename1+''
if @restrain!=''
begin
exec(@restrain)
end
FETCH NEXT FROM ChangeInvCodeCursor INTO @tablecolumn1,@tablename1
END
--关闭游标
CLOSE ChangeInvCodeCursor
DEALLOCATE ChangeInvCodeCursor

转载于:https://www.cnblogs.com/passerlee/p/5157154.html